countif question
 

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?

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

--

HTH

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


"floridasurfn" wrote in message
...
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?

On Sun, 27 Feb 2005 12:13:02 -0800, floridasurfn
wrote:

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

thanks for the help on this one

"floridasurfn" wrote:

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?