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