countif question

[Guest]

my data looks like this

A1: SUN B1: YES C1: NO
A2: HOME B2: YES C2: NO
A3: SUN B3: YES C3: YES
A4: HOME B4: YES C4: NO
A5: SUN B5: NO C5: NO
A6: SUN B6: YES C6: NO

I want to use a function to count if A1:A6 = SUN & B1:B6 = YES & C1:C6 =
NO
the answer i am looking for is (2) for this range.
Previously i was using =countif(A1:A6, "SUN") but can only search one
criteria
HOW DO I COUNT USING MULTIPLE CRITERIA?

 

[Bob Phillips]

=SUMPRODUCT(--(A1:A6="SUN"),--(B1:B6="YES"),--(C1:C6="NO"))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ron Rosenfeld]

my data looks like this

A1: SUN B1: YES C1: NO
A2: HOME B2: YES C2: NO
A3: SUN B3: YES C3: YES
A4: HOME B4: YES C4: NO
A5: SUN B5: NO C5: NO
A6: SUN B6: YES C6: NO

I want to use a function to count if A1:A6 = SUN & B1:B6 = YES & C1:C6 =
NO
the answer i am looking for is (2) for this range.
Previously i was using =countif(A1:A6, "SUN") but can only search one
criteria
HOW DO I COUNT USING MULTIPLE CRITERIA?

Here's one way:

=SUMPRODUCT((A1:A6="SUN")*(B1:B6="YES")*(C1:C6="NO"))


--ron

 

[Guest]

thanks for the help on this one