How to calculate sum of a column based on three categories in Excel-CodePudding

I have two table like this,

In this I want to find the total from the first table, based on the type in second table.

When it calculates the total, it should consider all the categories for the given type for the given month.

I have tried using sumifs but wasn't able to find a solution that I want.

I do not use excel every day, any help greatly appreciate.

CodePudding user response：

Try FILTER() with XLOOKUP() then SUM().

=SUM(XLOOKUP(FILTER($F$2:$F$6,$E$2:$E$6=I3),INDEX(FILTER($B$2:$C$7,$A$2:$A$7=$I$2),,1),INDEX(FILTER($B$2:$C$7,$A$2:$A$7=$I$2),,2),0,0))

CodePudding user response：

[I have two table like this,

In this I want to find the total from the first table, based on the type in second table.

When it calculates the total, it should consider all the categories for the given type for the given month.

I have tried using sumifs but wasn't able to find a solution that I want.

I do not use excel every day, any help greatly appreciate.]

I got the different answers, first two from (L-R side) are referred to helper column & the rest two without helper column. Hope this works to you.