| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fun with COUNT and AND functions.
Okay... i'm trying to count the amount of times 2 different values appear in
a row. ie basically, how many rows the number '4' occurs in column 'b' and 'toronto' occurs is column 'c'. it can only count rows where both cases are true. I just can't make it work. lil help? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Fun with COUNT and AND functions.
Try one of these:
=SUMPRODUCT((B10:B100="B")*(C10:C100="toronto")) or =SUMPRODUCT(--(B10:B100="B"),--(C10:C100="toronto")) Adjust range references to suit your situation (SUMPRODUCT doesn't work on entire columns) Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Johosh" wrote in message ... Okay... i'm trying to count the amount of times 2 different values appear in a row. ie basically, how many rows the number '4' occurs in column 'b' and 'toronto' occurs is column 'c'. it can only count rows where both cases are true. I just can't make it work. lil help? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Fun with COUNT and AND functions.
Darn...I typed B's instead of 4's:
Here're the corrected versions: =SUMPRODUCT((B10:B100=4)*(C10:C100="toronto")) or =SUMPRODUCT(--(B10:B100=4),--(C10:C100="toronto")) Again...Adjust range references to suit your situation -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Johosh" wrote in message ... Okay... i'm trying to count the amount of times 2 different values appear in a row. ie basically, how many rows the number '4' occurs in column 'b' and 'toronto' occurs is column 'c'. it can only count rows where both cases are true. I just can't make it work. lil help? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Fun with COUNT and AND functions.
Try this:
=SUMPRODUCT(--(B1:B20=4),--(C1:C20="toronto")) Adjust your range as needed. HTH, Paul -- "Johosh" wrote in message ... Okay... i'm trying to count the amount of times 2 different values appear in a row. ie basically, how many rows the number '4' occurs in column 'b' and 'toronto' occurs is column 'c'. it can only count rows where both cases are true. I just can't make it work. lil help? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Fun with COUNT and AND functions.
Is there any way to add wildcard values to this formula? it may be maditory
for what i need to do. "Ron Coderre" wrote: Darn...I typed B's instead of 4's: Here're the corrected versions: =SUMPRODUCT((B10:B100=4)*(C10:C100="toronto")) or =SUMPRODUCT(--(B10:B100=4),--(C10:C100="toronto")) Again...Adjust range references to suit your situation -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Johosh" wrote in message ... Okay... i'm trying to count the amount of times 2 different values appear in a row. ie basically, how many rows the number '4' occurs in column 'b' and 'toronto' occurs is column 'c'. it can only count rows where both cases are true. I just can't make it work. lil help? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Fun with COUNT and AND functions.
also, it may be linked to a different sheet within the same file.
"Ron Coderre" wrote: Darn...I typed B's instead of 4's: Here're the corrected versions: =SUMPRODUCT((B10:B100=4)*(C10:C100="toronto")) or =SUMPRODUCT(--(B10:B100=4),--(C10:C100="toronto")) Again...Adjust range references to suit your situation -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Johosh" wrote in message ... Okay... i'm trying to count the amount of times 2 different values appear in a row. ie basically, how many rows the number '4' occurs in column 'b' and 'toronto' occurs is column 'c'. it can only count rows where both cases are true. I just can't make it work. lil help? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Fun with COUNT and AND functions.
Perhaps this:
=SUMPRODUCT((B10:B100=4)*ISNUMBER(SEARCH("toronto" ,C10:C100))) If you're referencing another sheet...try this version: =SUMPRODUCT(('Sheet 01'!B10:B100=4)*ISNUMBER(SEARCH("toronto",'Sheet 01'!C10:C100))) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Johosh" wrote in message ... Is there any way to add wildcard values to this formula? it may be maditory for what i need to do. "Ron Coderre" wrote: Darn...I typed B's instead of 4's: Here're the corrected versions: =SUMPRODUCT((B10:B100=4)*(C10:C100="toronto")) or =SUMPRODUCT(--(B10:B100=4),--(C10:C100="toronto")) Again...Adjust range references to suit your situation -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Johosh" wrote in message ... Okay... i'm trying to count the amount of times 2 different values appear in a row. ie basically, how many rows the number '4' occurs in column 'b' and 'toronto' occurs is column 'c'. it can only count rows where both cases are true. I just can't make it work. lil help? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Fun with COUNT and AND functions.
That is EXACTLY what i need!
Thanks a lot! "Ron Coderre" wrote: Perhaps this: =SUMPRODUCT((B10:B100=4)*ISNUMBER(SEARCH("toronto" ,C10:C100))) If you're referencing another sheet...try this version: =SUMPRODUCT(('Sheet 01'!B10:B100=4)*ISNUMBER(SEARCH("toronto",'Sheet 01'!C10:C100))) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Johosh" wrote in message ... Is there any way to add wildcard values to this formula? it may be maditory for what i need to do. "Ron Coderre" wrote: Darn...I typed B's instead of 4's: Here're the corrected versions: =SUMPRODUCT((B10:B100=4)*(C10:C100="toronto")) or =SUMPRODUCT(--(B10:B100=4),--(C10:C100="toronto")) Again...Adjust range references to suit your situation -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Johosh" wrote in message ... Okay... i'm trying to count the amount of times 2 different values appear in a row. ie basically, how many rows the number '4' occurs in column 'b' and 'toronto' occurs is column 'c'. it can only count rows where both cases are true. I just can't make it work. lil help? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Fun with COUNT and AND functions.
I'm glad I could help!....thanks for letting me know.
*********** Regards, Ron XL2003, WinXP "Johosh" wrote: That is EXACTLY what i need! Thanks a lot! "Ron Coderre" wrote: Perhaps this: =SUMPRODUCT((B10:B100=4)*ISNUMBER(SEARCH("toronto" ,C10:C100))) If you're referencing another sheet...try this version: =SUMPRODUCT(('Sheet 01'!B10:B100=4)*ISNUMBER(SEARCH("toronto",'Sheet 01'!C10:C100))) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Johosh" wrote in message ... Is there any way to add wildcard values to this formula? it may be maditory for what i need to do. "Ron Coderre" wrote: Darn...I typed B's instead of 4's: Here're the corrected versions: =SUMPRODUCT((B10:B100=4)*(C10:C100="toronto")) or =SUMPRODUCT(--(B10:B100=4),--(C10:C100="toronto")) Again...Adjust range references to suit your situation -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Johosh" wrote in message ... Okay... i'm trying to count the amount of times 2 different values appear in a row. ie basically, how many rows the number '4' occurs in column 'b' and 'toronto' occurs is column 'c'. it can only count rows where both cases are true. I just can't make it work. lil help? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| functions to count Yes & No | Excel Worksheet Functions | |||
| Combining IF and Count functions | Excel Discussion (Misc queries) | |||
| Advanced Count functions | Excel Discussion (Misc queries) | |||
| Excel IF and COUNT functions | Excel Worksheet Functions | |||
| Using Dates in Count functions | Excel Worksheet Functions | |||
Linear Mode
