Lookup values in 2 columns, count and then multiply??? HELP!
 

Hi
I know it can be done but it escapes me at this stage. Please help!!!

I have a spreadsheet which looks like

A B
1 Dog 12/7/07
2 Cat 13/7/07
3 Dog
4 Dog 12/7/07
5 Cat
6 Dog 12/7/07
7 Dog 12/7/07


I want to return a count of the number of times that dog occurs in
column A and has a corresponding value in column B (is not blank) - so
in this case answer would be 4. I also then want to multiply the
answer by 150 (4*150).

Can someone help?

cheers
Debbie

Lookup values in 2 columns, count and then multiply??? HELP!
 

Try this:

=SUMPRODUCT((A1:A7="Dog")*(B1:B7<""))*150

Hope this helps.

Pete

On Jul 26, 2:28 pm, DebbieV wrote:
Hi
I know it can be done but it escapes me at this stage. Please help!!!

I have a spreadsheet which looks like

A B
1 Dog 12/7/07
2 Cat 13/7/07
3 Dog
4 Dog 12/7/07
5 Cat
6 Dog 12/7/07
7 Dog 12/7/07

I want to return a count of the number of times that dog occurs in
column A and has a corresponding value in column B (is not blank) - so
in this case answer would be 4. I also then want to multiply the
answer by 150 (4*150).

Can someone help?

cheers
Debbie

Lookup values in 2 columns, count and then multiply??? HELP!
 

=SUMPRODUCT(--(a2:a100="Dog"),--(b2:B100<""))*150

Better to put "Dog" in a cell:

=SUMPRODUCT(--(a2:a100=X1),--(b2:B100<""))*150

HTH

"DebbieV" wrote:

Hi
I know it can be done but it escapes me at this stage. Please help!!!

I have a spreadsheet which looks like

A B
1 Dog 12/7/07
2 Cat 13/7/07
3 Dog
4 Dog 12/7/07
5 Cat
6 Dog 12/7/07
7 Dog 12/7/07


I want to return a count of the number of times that dog occurs in
column A and has a corresponding value in column B (is not blank) - so
in this case answer would be 4. I also then want to multiply the
answer by 150 (4*150).

Can someone help?

cheers
Debbie

Lookup values in 2 columns, count and then multiply??? HELP!
 

On Jul 26, 11:44 pm, Toppers
wrote:
=SUMPRODUCT(--(a2:a100="Dog"),--(b2:B100<""))*150

Better to put "Dog" in a cell:

=SUMPRODUCT(--(a2:a100=X1),--(b2:B100<""))*150

HTH



"DebbieV" wrote:
Hi
I know it can be done but it escapes me at this stage. Please help!!!

I have a spreadsheet which looks like

A B
1 Dog 12/7/07
2 Cat 13/7/07
3 Dog
4 Dog 12/7/07
5 Cat
6 Dog 12/7/07
7 Dog 12/7/07

I want to return a count of the number of times that dog occurs in
column A and has a corresponding value in column B (is not blank) - so
in this case answer would be 4. I also then want to multiply the
answer by 150 (4*150).

Can someone help?

cheers
Debbie- Hide quoted text -

- Show quoted text -

Thanks Pete - worked beautifully!

Lookup values in 2 columns, count and then multiply??? HELP!
 

Thanks for the feedback (although you responded to Toppers' post)

Pete

On Jul 26, 11:34 pm, DebbieV wrote:
On Jul 26, 11:44 pm, Toppers
wrote:





=SUMPRODUCT(--(a2:a100="Dog"),--(b2:B100<""))*150

Better to put "Dog" in a cell:

=SUMPRODUCT(--(a2:a100=X1),--(b2:B100<""))*150

HTH

"DebbieV" wrote:
Hi
I know it can be done but it escapes me at this stage. Please help!!!

I have a spreadsheet which looks like

A B
1 Dog 12/7/07
2 Cat 13/7/07
3 Dog
4 Dog 12/7/07
5 Cat
6 Dog 12/7/07
7 Dog 12/7/07

I want to return a count of the number of times that dog occurs in
column A and has a corresponding value in column B (is not blank) - so
in this case answer would be 4. I also then want to multiply the
answer by 150 (4*150).

Can someone help?

cheers
Debbie- Hide quoted text -

- Show quoted text -

Thanks Pete - worked beautifully!- Hide quoted text -

- Show quoted text -