| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding values within multiply columns | Excel Discussion (Misc queries) | |||
| can vlookup be used to if the lookup values are in two columns? | Excel Worksheet Functions | |||
| Formula to count values in two columns | Excel Discussion (Misc queries) | |||
| I would like to Count values in two columns. | Excel Worksheet Functions | |||
| How do I count values across multiple columns? | Excel Worksheet Functions | |||
Linear Mode
