Count all occurrences matching categories of list

[grogoreo]

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.

[EarnestAl]

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

[newbie-02]

hello @grogoreo,

‘sumproduct’ will be your friend,

from:

https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_SUMPRODUCT_function

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?

[grogoreo]

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

[newbie-02]

at @grogoreo:
if the answer solved your problem just mark it ‘correct’ by clicking on the grey dot below,
^
0
v
left to the answer,
it would help others to see where to find solutions
if you ‘like’ it give it an ‘upvote’ by clicking the ‘^’,
stay healthy …