How to simplify this SUMPRODUCT formula, --(B2:B7="Sunday") + --(B2:B7="Monday")

Question

I need an array as a filter in a SUMPRODUCT formula. I'd like to pick up either Sunday or Monday in B2:B7 and ignore the rest (this is an simplified example). The formula I have is this:

=SUMPRODUCT(--(B2:B7="Sunday")+--(B2:B7="Monday"),........)

--(B2:B7="Sunday") gives an array of {1,0,0,0,0,0,0} and --(B2:B7="Monday") gives {0,1,0,0,0,0,0}. The sum of these two arrays is {1,1,0,0,0,0,0}. When used in SUMPRODUCT, this array would filter off others, leaving only Sunday and Monday entries. I wonder if there is a direct way of getting this array. I have tried --(B2:B7="Sunday","Monday") but it gives a two dimensional array, hence, cannot be used.

Answer 1

If we have to check cells in single range for multiple values, as in case of CountIfs, SumIfs SumProduct functions; we can can use (Range = {"Criteria1","Criteria2"}) argument in the function.

In your case, because you'd like to pick up either Sunday or Monday in B2:B7 and ignore the rest, Try (B2:B7={"Sunday","Monday"}) So, the function will be =SUMPRODUCT((B2:B7={"Sunday","Monday"}),........)

I learnt this from Tina, a new contributor to SO.. Refer excel 2016 combine subtotal with sumifs

Answer 2

Try:

=SUMPRODUCT((Table1[Column2]="Monday")*(Table1[Column4]))+SUMPRODUCT((Table1[Column2]="Sunday")*(Table1[Column4]))