How can I count data based on two conditions in a series of columns?

[excelsearcher]

Hello,

I am an Excel novice and would appreciate any advice on this problem. I am trying to evaluate which zipcodes are generating leads and which are not over a period of time by counting appointment results based on zipcode. My data looks something like this:

A B C D E F
Zipcode Appt Result 2nd Appt Result 3rd Appt Result
48103 10/1/2013 No Show 10/7/2013 No Show 10/14/2013 No Show
48104 10/1/2013 No Show 10/7/2013 Showed & Retained
48103 10/1/2013 No Show 10/7/2013 No Show 10/14/2013 No Show
48104 10/1/2013 Showed & Retained
48103 10/1/2013 No Show 10/7/2013 No Show 10/14/2013 No Show
48104 10/1/2013 Showed & Retained

I tried to use the countifs function: =COUNTIFS(A2:A7,"48103",C2:G7,"No Show") but I get a #VALUE! error message. Am I messing up the function structure? Is there a better function to use?

[Tony Valko]

Try it like this...

=SUMPRODUCT((A2:A7=48103)*(C2:G7="no show"))

Better to use cells to hold the criteria:

A20 = 48103
B20 = no show

=SUMPRODUCT((A2:A7=A20)*(C2:G7=B20))

[excelsearcher]

You are a genius! Thank you so much! God bless you!

[Tony Valko]

You're welcome. Thanks for the feedback!

[FDibbins]

Not sure if you are still monitoring this, but if your zip codes are values (test with =isnumber(), should get a TRUE if they are), then you just need to remove the ""...

=COUNTIFS(A2:A7,48103,C2:G7,"No Show")

[Tony Valko]

With the COUNTIFS function, the ranges must be the same size.