Ask Your Question
0

Count all occurrences matching categories of list

asked 2020-06-26 18:45:32 +0200

grogoreo gravatar image

I use a spreadsheet for budgeting and have individual sheets containing transactions for each year and another sheet categorising strings for my outgoings. For example
A2=Food | B2=ALDI
A3=Food | B2=TESCO
A4=Utilities | B4=BRITISH GAS
etc.

Each of the values in the B column should partly match the string on my transactions sheet A1=21/4/2020 | B1=42343 LIDL GB, LIDL, GB | C1=-21.50

What I'd like to do is count all the occurrences of, for example, LIDL and ALDI in a month under the category of 'Food' so I can say 'x' amount is spent of Food, Utilities, Eating Out etc.

edit retag flag offensive close merge delete

Comments

Cell B1 in your transactions sheet appears to have some some comma separated fields. Can you separate further when you do a Text import for this from a csv supplied from the bank? It would be easier to sort or construct formulas if the company were in a separate column from transaction number and from country.

In your sheet listing categories, rather than repeating the category name (A2=Food) which can lead to errors of spelling or near duplicate categories you might be better having a column in which the Food retailers are listed and another column for Utilities, one for Clothing, etc.

I would have thought that the type of transaction should be against the transaction itself. What happens, for example, if Tesco sell more than just food, such as office supplies (printing paper), garden furniture, beauty products, clothes, takeaways? If you need to split consider something like gnucash

Earnest Al gravatar imageEarnest Al ( 2020-06-29 02:18:00 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2020-06-28 12:18:58 +0200

newbie-02 gravatar image

hello @grogoreo,

'sumproduct' will be your friend,

from:

https://wiki.openoffice.org/wiki/Docu...

SUMPRODUCT(A1:A6="red"; B1:B6="big"; C1:C6)

returns the sum of cells in C1:C6 whose corresponding entries in the A column are red and in the B column are big. This is not portable to Excel, as Excel ignores logical values.

besides: 'B2=TESCO' is a typo?

edit flag offensive delete link more

Comments

Thanks very much. Yes it was a typo, it was meant to be B3.

grogoreo gravatar imagegrogoreo ( 2020-06-28 14:55:30 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-06-26 18:45:32 +0200

Seen: 27 times

Last updated: Jun 28