home

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 2Fixed (hard coded) string searchIn 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 StarbucksDescription keywords contained in a listPlace 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 FUncategorised itemsAssuming 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 columnMonthly totalsAssuming 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