Donate to support site

home

Excel - Sum conditionally containing text string
Author Nigel Rivett

The aim is to take three columns containing date, description, amount and to create a sum by category i.e. categorise and sum the values
We will look to sum amounts for descriptions that are for coffee descriptions
```Setup
Column A contains the date
Column B contains the description
Column C contains the amount

The value details start at row 2

Fixed (hard coded) string search

In cell E2 place the formula
=IF(ISNUMBER(SEARCH("Starbucks",B2)),C2,"")
Copy this down to fill all cells in the column
This will place the amount in the E column if the desctiption contains Starbucks

Description keywords contained in a list

Place these values in column K
Cell K2 Starbucks
Cell K3 Costa
Cell K4 Nero
Cell K5-K10 Nero      these are dummy values that can be replaced

In cell E2 place the formula
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(K\$2:K\$10,\$B2))),\$C2,"")
Copy this down to fill all cells in the column
This will then place the amount in the E column if the description contains any of Starbucks, Costa, Nero
To add more keywords replace the dummy values

To add more categorisation place another keyword list in column L and copy column E to column F

Uncategorised items

Assuming there are categorised items in columns E,F,G
In cell D2 place the formula
=IF(SUM(E2:G2)=0,C2,"")
Copy this down to fill all cells in the column

Monthly totals

Assuming the entries are in ascending date order
This formula will give a column with the total for that month allocated to the last entry row for that month