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.

=SUMPRODUCT(--(G1:G7="Smith"),--(K1:K70),--(S1:S70),S1:S7)

"Terri" wrote:

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.

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:K70),--(S1:S70))

Regards,
Paul

--

"Sean Timmons" wrote in message
...
=SUMPRODUCT(--(G1:G7="Smith"),--(K1:K70),--(S1:S70),S1:S7)

"Terri" wrote:

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.

=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

--

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

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)

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

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


"Terri" wrote:

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.

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

=SUMPRODUCT((A1:A10="Smith")*(B1:B10*C1:C100))

Rick


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