Count/sumproduct
 

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.

Count/sumproduct
 

=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.

Count/sumproduct
 

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.

Count/sumproduct
 

=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.

Count/sumproduct
 

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.

Count/sumproduct
 

=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.

Count/sumproduct
 

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.