SUMIF with listdown criteria

Question

I have a 3 columns naming Team,Side,Score. Then I created a Listdown which has All,Home, and Away then that will be my criteria in my SUMIF.

so. =SUMIF(B2:B100,D1) where D1 is the listdown cell. My goal here is to sum if I choose home or away or either(all). But my Side column only contains Home and Away. What will I do if I want to sum both Home and Away if I pick All in the List? Sorry, I can't explain well.

Answer 1

Assuming that you want to sum the score, the formula should look more like this:

=SUMIF(B2:B100,D1,C2:C100)

For the "All" you can go for "*" like:

=SUMIF(B2:B100,IF(D1="All","*",D1),C2:C100)

This should do the trick ;)

EDIT:
If you just want to count the games then:

=COUNTIF(B2:B100,IF(D1="All","*",D1))

is all you need...

Answer 2

Use this array formula finalized with ctr+shift+enter, not just enter.

'for the count
=SUM(COUNTIF(B:B, IF(D1="all", {"home","away"}, D1)))
'for the sum of scores in column c
=SUM(SUMIFS(C:C, B:B, IF(D1="all", {"home","away"}, D1)))