Sum with two conditions in Excel Table

Question

I'm new to this forum, so if I make mistakes, tell me so I can learn ;).

So the question is, I want to make a summation of a column of a table in Excel, but only if it complies with two conditions. Table1 has 3 columns: Col1 contains a Date, Col2 a price and Col3 a catagory in which the price is logged.

I want the sum of all prices, for which the date falls within a certain month, and the Category complies with a choosen Category. The code for both individual requirements works, and looks like this:

{=SUM(IF(MONTH(Table1[Date])=MONTH(A3);Table1[Price];0))}
{=SUM(IF(Table1[Category]="Category1";Table1[Price];0))}

However, the combined sum, =SUM(IF(AND(MONTH(Table1[Date])=MONTH(A3);Table1[Category]="Category1");Table1[Price];0)) does not work.

Do you know what I do wrong?

Thanks in advance

Answer 1

I think not really "do wrong" though perhaps a poor choice of approach (in my opinion, I happen not to be a fan of structured references). It is just that AND here does not return an array, only either TRUE (when the result would be the sum of all prices) or, much more likely FALSE (any one condition does not match, when the result is 0).

Instead I would recommend a PivotTable.