| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Count Condition
Hello all,
I'm trying to create a multiple condition count and I can't get it to work using all the examples I've found. I'm hope someone can help me out. Here's the formula I've trying to manipulate (unsuccessfully): =COUNT(('Grindex 2008'!AB:AB="Jan-2")*('Grindex 2008'!AB:AB="Jan-3")*('Grindex 2008'!Y:Y="Jan-D")) So if (column AB = "Jan-2" OR column AB = "Jan-3") AND column Y = "Jan-D" then count the row. I'm drawing a complete blank. Anyone know the correct format? I can't see to find a good example on how to structure the formula. Thanks, Jay -- Disregard, this is so I can find my post later. ***postedbyJay*** |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple Count Condition
I also tried this but it doesn't produce the expected value.
=COUNT(IF(('Grindex 2008'!AB:AB="Jan-2")*('Grindex 2008'!AB:AB="Jan-3"),IF('Grindex 2008'!Y:Y="Jan-D",'Grindex 2008'!Y:Y))) -- Disregard, this is so I can find my post later. ***postedbyJay*** "Jay" wrote: Hello all, I'm trying to create a multiple condition count and I can't get it to work using all the examples I've found. I'm hope someone can help me out. Here's the formula I've trying to manipulate (unsuccessfully): =COUNT(('Grindex 2008'!AB:AB="Jan-2")*('Grindex 2008'!AB:AB="Jan-3")*('Grindex 2008'!Y:Y="Jan-D")) So if (column AB = "Jan-2" OR column AB = "Jan-3") AND column Y = "Jan-D" then count the row. I'm drawing a complete blank. Anyone know the correct format? I can't see to find a good example on how to structure the formula. Thanks, Jay -- Disregard, this is so I can find my post later. ***postedbyJay*** |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple Count Condition
One possible way:
=SUMPRODUCT(--('Grindex 2008'!AB1:AB100="Jan-2"),--('Grindex 2008'!Y1:Y100="Jan-D"))+SUMPRODUCT(--('Grindex 2008'!AB1:AB100="Jan-3"),--('Grindex 2008'!Y1:Y100="Jan-D")) HTH, Paul -- "Jay" wrote in message ... Hello all, I'm trying to create a multiple condition count and I can't get it to work using all the examples I've found. I'm hope someone can help me out. Here's the formula I've trying to manipulate (unsuccessfully): =COUNT(('Grindex 2008'!AB:AB="Jan-2")*('Grindex 2008'!AB:AB="Jan-3")*('Grindex 2008'!Y:Y="Jan-D")) So if (column AB = "Jan-2" OR column AB = "Jan-3") AND column Y = "Jan-D" then count the row. I'm drawing a complete blank. Anyone know the correct format? I can't see to find a good example on how to structure the formula. Thanks, Jay -- Disregard, this is so I can find my post later. ***postedbyJay*** |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple Count Condition
Paul,
The example you gave works well, but I have a question. Why do I need to specify the rows in the formula? I tried this, And I get a #NUM error. =SUMPRODUCT(--('Grindex 2008'!AB:AB="Jan-2"),--('Grindex 2008'!Y:Y="Jan-D"))+SUMPRODUCT(--('Grindex 2008'!AB:AB="Jan-3"),--('Grindex 2008'!Y:Y="Jan-D")) The only way I can try to get all rows is to use this: =SUMPRODUCT(--('Grindex 2008'!AB1:AB65535="Jan-2"),--('Grindex 2008'!Y1:Y65535="Jan-D"))+SUMPRODUCT(--('Grindex 2008'!AB1:AB65535="Jan-3"),--('Grindex 2008'!Y1:Y65535="Jan-D")) Any ideas? Thanks, Jay -- Disregard, this is so I can find my post later. ***postedbyJay*** "PCLIVE" wrote: One possible way: =SUMPRODUCT(--('Grindex 2008'!AB1:AB100="Jan-2"),--('Grindex 2008'!Y1:Y100="Jan-D"))+SUMPRODUCT(--('Grindex 2008'!AB1:AB100="Jan-3"),--('Grindex 2008'!Y1:Y100="Jan-D")) HTH, Paul -- "Jay" wrote in message ... Hello all, I'm trying to create a multiple condition count and I can't get it to work using all the examples I've found. I'm hope someone can help me out. Here's the formula I've trying to manipulate (unsuccessfully): =COUNT(('Grindex 2008'!AB:AB="Jan-2")*('Grindex 2008'!AB:AB="Jan-3")*('Grindex 2008'!Y:Y="Jan-D")) So if (column AB = "Jan-2" OR column AB = "Jan-3") AND column Y = "Jan-D" then count the row. I'm drawing a complete blank. Anyone know the correct format? I can't see to find a good example on how to structure the formula. Thanks, Jay -- Disregard, this is so I can find my post later. ***postedbyJay*** |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple Count Condition
You cannot use full column ranges when using SUMPRODUCT. You must specify a
range. -- "Jay" wrote in message ... Paul, The example you gave works well, but I have a question. Why do I need to specify the rows in the formula? I tried this, And I get a #NUM error. =SUMPRODUCT(--('Grindex 2008'!AB:AB="Jan-2"),--('Grindex 2008'!Y:Y="Jan-D"))+SUMPRODUCT(--('Grindex 2008'!AB:AB="Jan-3"),--('Grindex 2008'!Y:Y="Jan-D")) The only way I can try to get all rows is to use this: =SUMPRODUCT(--('Grindex 2008'!AB1:AB65535="Jan-2"),--('Grindex 2008'!Y1:Y65535="Jan-D"))+SUMPRODUCT(--('Grindex 2008'!AB1:AB65535="Jan-3"),--('Grindex 2008'!Y1:Y65535="Jan-D")) Any ideas? Thanks, Jay -- Disregard, this is so I can find my post later. ***postedbyJay*** "PCLIVE" wrote: One possible way: =SUMPRODUCT(--('Grindex 2008'!AB1:AB100="Jan-2"),--('Grindex 2008'!Y1:Y100="Jan-D"))+SUMPRODUCT(--('Grindex 2008'!AB1:AB100="Jan-3"),--('Grindex 2008'!Y1:Y100="Jan-D")) HTH, Paul -- "Jay" wrote in message ... Hello all, I'm trying to create a multiple condition count and I can't get it to work using all the examples I've found. I'm hope someone can help me out. Here's the formula I've trying to manipulate (unsuccessfully): =COUNT(('Grindex 2008'!AB:AB="Jan-2")*('Grindex 2008'!AB:AB="Jan-3")*('Grindex 2008'!Y:Y="Jan-D")) So if (column AB = "Jan-2" OR column AB = "Jan-3") AND column Y = "Jan-D" then count the row. I'm drawing a complete blank. Anyone know the correct format? I can't see to find a good example on how to structure the formula. Thanks, Jay -- Disregard, this is so I can find my post later. ***postedbyJay*** |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple Count Condition
Ah, didn't realize that.
Thanks! -- Disregard, this is so I can find my post later. ***postedbyJay*** "PCLIVE" wrote: You cannot use full column ranges when using SUMPRODUCT. You must specify a range. -- "Jay" wrote in message ... Paul, The example you gave works well, but I have a question. Why do I need to specify the rows in the formula? I tried this, And I get a #NUM error. =SUMPRODUCT(--('Grindex 2008'!AB:AB="Jan-2"),--('Grindex 2008'!Y:Y="Jan-D"))+SUMPRODUCT(--('Grindex 2008'!AB:AB="Jan-3"),--('Grindex 2008'!Y:Y="Jan-D")) The only way I can try to get all rows is to use this: =SUMPRODUCT(--('Grindex 2008'!AB1:AB65535="Jan-2"),--('Grindex 2008'!Y1:Y65535="Jan-D"))+SUMPRODUCT(--('Grindex 2008'!AB1:AB65535="Jan-3"),--('Grindex 2008'!Y1:Y65535="Jan-D")) Any ideas? Thanks, Jay -- Disregard, this is so I can find my post later. ***postedbyJay*** "PCLIVE" wrote: One possible way: =SUMPRODUCT(--('Grindex 2008'!AB1:AB100="Jan-2"),--('Grindex 2008'!Y1:Y100="Jan-D"))+SUMPRODUCT(--('Grindex 2008'!AB1:AB100="Jan-3"),--('Grindex 2008'!Y1:Y100="Jan-D")) HTH, Paul -- "Jay" wrote in message ... Hello all, I'm trying to create a multiple condition count and I can't get it to work using all the examples I've found. I'm hope someone can help me out. Here's the formula I've trying to manipulate (unsuccessfully): =COUNT(('Grindex 2008'!AB:AB="Jan-2")*('Grindex 2008'!AB:AB="Jan-3")*('Grindex 2008'!Y:Y="Jan-D")) So if (column AB = "Jan-2" OR column AB = "Jan-3") AND column Y = "Jan-D" then count the row. I'm drawing a complete blank. Anyone know the correct format? I can't see to find a good example on how to structure the formula. Thanks, Jay -- Disregard, this is so I can find my post later. ***postedbyJay*** |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lookup with multiple condition, but one condition to satisfy is en | Excel Worksheet Functions | |||
| helps with count under multiple condition | Excel Discussion (Misc queries) | |||
| Count multiple condition | Excel Programming | |||
| What formula/fn would I use to count multiple condition records? | Excel Worksheet Functions | |||
| how to count unique entries with multiple condition | Excel Worksheet Functions | |||
Linear Mode
