Count or Sum - Assistance

[Ecksel]

Hello MrExcel,
Long time reader, first time poster.

Let me set this up:
I have 4 columns A thru D.

Column A = Yes or No
Column B = Apples, Oranges, Bananas, Pears, Grapes
Column C = Farmers Market, Grocery Store, Farm
Column D = (Age of fruit) in numbers

In a summary tab, I'm trying to put together a formula to count the follow (as an example):

How many are labeled YES in ColA and are Apples in ColB and are from either the Grocery Store or Farm and are over 15 days old.

I can't seem to get the formula(s) to work.

Anyone's assistance is greatly appreciated.

FYI - I can get the CountIf to work if I'm choosing ONE (1) criteria from ColC, but not multiple.

Thanks in advance

 

[Colin Legg]

Hi and welcome to the forum.

In Excel 2007 and later:

=SUM(COUNTIFS(A:A,"Yes",B:B,"Apples",C:C,{"Grocery Store","Farm"},D:D,">15"))

In Excel 2003 and earlier*:

=SUMPRODUCT(--(A2:A100="Yes"),--(B2:B100="Apples"),
                    --(C2:C100={"Grocery Store","Farm"}),--(D2:D100>15))

*note you cannot use entire column references such as A:A.

 

[Ecksel]

I'm getting a #VALUE! response, unfortunately in Excel 2003.

 

[Colin Legg]

Sorry, that was my mistake. Try this instead:

=SUMPRODUCT((A2:A100="Yes")*(B2:B100="Apples")*(C2:C100={"Grocery Store","Farm"})*(D2:D100>15))