SUMPRODUCT or COUNTIF for weekday + multiple text criteria?

[tsusan]

In the following table I would like to count how many times EITHER 'apple' OR 'pear' appears on a Saturday. (Note that sometimes BOTH 'apple' and 'pear' appear on a Saturday, but they should only be counted once. So the correct answer should yield 3 out of the 4 Saturdays in this table.)

Column A uses "date" formatting. Columns B and C are text only.

I'm pretty sure I want to use a SUMPRODUCT with some kind of WEEKDAY syntax for Column A and some kind of multiple text criteria for Columns B and C, but I'm not sure how to do that. I know how to COUNTIF multiple text criteria, but I don't know how to SUMPRODUCT that with the Column A data.

Thanks in advance.

A	B	C
Saturday, July 22, 2017 <tbody> </tbody>	apple	kiwi
Friday, July 21, 2017 <tbody> </tbody>	jackfruit	durian
Thursday, July 20, 2017 <tbody> </tbody>	kiwi	jackfruit
Wednesday, July 19, 2017 <tbody> </tbody>	kiwi	kiwi
Tuesday, July 18, 2017 <tbody> </tbody>	apple	kiwi
Monday, July 17, 2017 <tbody> </tbody>	jackfruit	kiwi
Sunday, July 16, 2017 <tbody> </tbody>	kiwi	pear
Saturday, July 15, 2017 <tbody> </tbody>	pear	apple
Friday, July 14, 2017 <tbody> </tbody>	kiwi	kiwi
Thursday, July 13, 2017 <tbody> </tbody>	durian	jackfruit
Wednesday, July 12, 2017 <tbody> </tbody>	kiwi	kiwi
Tuesday, July 11, 2017 <tbody> </tbody>	kiwi	kiwi
Monday, July 10, 2017 <tbody> </tbody>	jackfruit	durian
Sunday, July 9, 2017 <tbody> </tbody>	kiwi	kiwi
Saturday, July 8, 2017 <tbody> </tbody>	kiwi	kiwi
Friday, July 7, 2017 <tbody> </tbody>	apple	kiwi
Thursday, July 6, 2017 <tbody> </tbody>	jackfruit	kiwi
Wednesday, July 5, 2017 <tbody> </tbody>	kiwi	pear
Tuesday, July 4, 2017 <tbody> </tbody>	durian	kiwi
Monday, July 3, 2017 <tbody> </tbody>	durian	jackfruit
Sunday, July 2, 2017 <tbody> </tbody>	pear	durian
Saturday, July 1, 2017 <tbody> </tbody>	pear	jackfruit

<tbody>
</tbody>

 

[Tetra201]

Maybe

=SUMPRODUCT(--(WEEKDAY(A2:A23)=7),SIGN((B2:B23="apple")+(C2:C23="apple")+(B2:B23="pear")+(C2:C23="pear")))

 

[Dave Patton]

removed

 

[Dave Patton]

My suggestion works but I like Tetra201's suggestion.

=SUMPRODUCT(--(WEEKDAY(A2:A23)=7),--(((B2:B23="apple")+(B2:B23="pear")+(C2:C23="apple")+(C2:C23="pear"))>0))

 

[tsusan]

Thank you both. What if Column B & C are a named range? Could I sub in the named range for B2:B23 and/or C2:C23? Should that work just as effectively?

 

[Tetra201]

Don't see why not -- as long as the ranges are of the same size and do not cover the entire column.

 

[tsusan]

Alas, it doesn't seem to like it with a named range. Here's what I was actually trying to do based on your model (I used apples and pears to keep it simple in the original example, but my real data is short alphanumeric combos).

This is what worked:
=SUMPRODUCT(--(WEEKDAY(A2:A209)=7),SIGN((B2:B209="XXI")+(C2:C209="XXI")+(B2:B209="8C")+(C2:C209="8C")+(B2:B209="7D")+(C2:C209="7D")+(B2:B209="5W")+(C2:C209="5W")+(B2:B209="3S")+(C2:C209="3S")+(B2:B209="10W")+(C2:C209="10W")))


This is what didn't work, after I tried to sub B2:B209 and C2:C209 with the named range (which covers B2:C209):

=SUMPRODUCT(--(WEEKDAY(A2:A209)=7),SIGN((S2017Cards="XXI")+(S2017Cards="8C")+(S2017Cards="7D")+(S2017Cards="5W")+(S2017Cards="3S")+(S2017Cards="10W")))

The named range is working fine in a number of other formulas on the sheet, so I don't think it's the problem. I don't have to use it here - I just hoped I could because it seemed a little more elegant than the Column B + Column C solution. But clearly there's something Excel doesn't like about it!

 

[Dave Patton]

with your original example and named ranges

=SUMPRODUCT(--(WEEKDAY(rngA)=7),--(((rngB="apple")+(rngB="pear")+(rngC="apple")+(rngC="pear"))>0))

N.B. Each named range is the same length.

Try a distinct named range for each column.

 

[tsusan]

That seems to work! Thanks!

I guess the named range has to cover exactly the same number of cells in each of the components.

with your original example and named ranges

=SUMPRODUCT(--(WEEKDAY(rngA)=7),--(((rngB="apple")+(rngB="pear")+(rngC="apple")+(rngC="pear"))>0))

N.B. Each named range is the same length.

Try a distinct named range for each column.