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
This is useful for maintaining expenditure details from downloaded account transactions
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
Add to column D2
=IF(OR(MONTH($A2)<>MONTH($A3),$A3=""),SUMIFS(C$2:C2, A$2:A2,">" & EOMONTH(A2,-1), A$2:A2,"<=" &  EOMONTH(A2,0)),"")

A bit of explanation
OR(MONTH($A2)<>MONTH($A3),$A3="")	Add the entry if the next row does not have the same month as the currenbt month
SUMIFS(C$2:C2				sum all the amount values in the range for the given conditions
A$2:A2,">" & EOMONTH(A2,-1)		Select all amounts for which the date is greater than the end of the previous month 
A$2:A2,"<=" &  EOMONTH(A2,0))		Select all amounts for which the date is less than or equal to the end of the current month

Copy this down to fill all cells in the column


home