Count/sumproduct

[Terri]

I have a spreadsheet that I need to find "Smith", then count if both dog and
cat appear for the same row, i.e.
(G) (K) (dog) (S) (cat)
Smith 1 1
Smith 1
Smith 1
Jones 1 1
Nelson 1 1
Anderson 1 1
Smith 1 1

The answer in this particular example should be 2 (there are two rows of
Smith that meet the criteria)

Any assistance would be greatly appreciated. Thanks.

 

[Sean Timmons]

=SUMPRODUCT(--(G1:G7="Smith"),--(K1:K7>0),--(S1:S7>0),S1:S7)

 

[Ron Coderre]

Try something like this:

=SUMPRODUCT(--(G2:G10="Smith"),--(K2:K10=1),--(S2:S10=1))

You could also reference cells, instead of entering the criteria directly:
A1: Smith
B1: 1
C1: 1

=SUMPRODUCT(INDEX(COUNTIF(A1,G2:G10),0),--(K2:K10=B1),--(S2:S10=C1))

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[PCLIVE]

=SUMPRODUCT(--(G1:G10="Smith"),--(K1:K10="dog"),--(S1:S10="cat"))

If you have the criteria in cells:
A1=Smith
B1=dog
C1=cat

then:
=SUMPRODUCT(--(G1:G10=A1),--(K1:K10=B1),--(S1:S10=C1))

HTH,
Paul

 

[PCLIVE]

The sum range is not necessary based on the OP's request. This is also with
the assumption that cat and dog is represented by a number greater than
zero.

=SUMPRODUCT(--(G1:G7="Smith"),--(K1:K7>0),--(S1:S7>0))

Regards,
Paul

--

 

[Jim May]

=SUMPRODUCT(--($G$2:$G$8="Smith"),--($K$2:$K$8=1),--($S$2:$S$8=1))

 

[Rick Rothstein \(MVP - VB\)]

Same concept as the others, just implemented differently...

=SUMPRODUCT((A1:A10="Smith")*(B1:B10*C1:C10>0))

Rick