| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula too long
Hi,
This is a repost...Thanks Don and Julie but solutions did not work... I'm trying to count with multiple criterias (I'm only showing 3...but I have 35 criterias...Below is my formula...when I put all of my criterias...i have a "Formula too long" message...Please help...I dont want Pivot table because I want dynamic data...Thanks. Paul =SUMPRODUCT(($A$1:$A$5000="Apples")*(($B$1:$B$5000 ="Good") + ($B$1:$B$5000="Very Good")+ ($B$1:$B$5000="Bad"))) Should give me 3 as a results A B Apples Good Apples Very good Oranges Good Oranges Very good Oranges Very good Apples Super Apples Bad |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula too long
Just a quick response. Must dash, no time to think or test, but perhaps try
naming the ranges, using a very short range name, such as A_1 refers to $A$1:$A$5000. Then refer to the names in the formula. This may reduce the number of characters required in the formula. "Paul" wrote in message ... Hi, This is a repost...Thanks Don and Julie but solutions did not work... I'm trying to count with multiple criterias (I'm only showing 3...but I have 35 criterias...Below is my formula...when I put all of my criterias...i have a "Formula too long" message...Please help...I dont want Pivot table because I want dynamic data...Thanks. Paul =SUMPRODUCT(($A$1:$A$5000="Apples")*(($B$1:$B$5000 ="Good") + ($B$1:$B$5000="Very Good")+ ($B$1:$B$5000="Bad"))) Should give me 3 as a results A B Apples Good Apples Very good Oranges Good Oranges Very good Oranges Very good Apples Super Apples Bad |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula too long
What I sent will work to shorten the formula but maybe not all of your 35.
You have 35 conditions out of ______how many. Did you see my suggestion for using < (not equal to) =SUMPRODUCT(($A$1:$a$5000="Apples")*($B$1:$B$5000< "Really Bad")) or -- Don Guillett SalesAid Software "Paul" wrote in message ... Hi, This is a repost...Thanks Don and Julie but solutions did not work... I'm trying to count with multiple criterias (I'm only showing 3...but I have 35 criterias...Below is my formula...when I put all of my criterias...i have a "Formula too long" message...Please help...I dont want Pivot table because I want dynamic data...Thanks. Paul =SUMPRODUCT(($A$1:$A$5000="Apples")*(($B$1:$B$5000 ="Good") + ($B$1:$B$5000="Very Good")+ ($B$1:$B$5000="Bad"))) Should give me 3 as a results A B Apples Good Apples Very good Oranges Good Oranges Very good Oranges Very good Apples Super Apples Bad |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula too long
Thanks...I have 35 condition in a row of 200
values...tries searching the web in vain... Paul -----Original Message----- What I sent will work to shorten the formula but maybe not all of your 35. You have 35 conditions out of ______how many. Did you see my suggestion for using < (not equal to) =SUMPRODUCT(($A$1:$a$5000="Apples")* ($B$1:$B$5000<"Really Bad")) or -- Don Guillett SalesAid Software "Paul" wrote in message ... Hi, This is a repost...Thanks Don and Julie but solutions did not work... I'm trying to count with multiple criterias (I'm only showing 3...but I have 35 criterias...Below is my formula...when I put all of my criterias...i have a "Formula too long" message...Please help...I dont want Pivot table because I want dynamic data...Thanks. Paul =SUMPRODUCT(($A$1:$A$5000="Apples")* (($B$1:$B$5000="Good") + ($B$1:$B$5000="Very Good")+ ($B$1:$B$5000="Bad"))) Should give me 3 as a results A B Apples Good Apples Very good Oranges Good Oranges Very good Oranges Very good Apples Super Apples Bad . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula too long
Hi Paul
i dont' seem to be seeing all of your posts ... however, the SUMPRODUCT function only allows for 30 parameters ... so you'll need to find another way to achieve your goal. Unsure of why you think that a pivot table isn't going to give you dynamic data. If you'ld like to explain the data you have (i'm guessing its not apples & oranges) and what you're trying to achieve (and answer Don's question) we might have a better idea of a solution. Cheers JulieD "Don Guillett" wrote in message ... The question was You have 35 conditions out of how many conditions, not rows! I assume that of the 200 rows that there are many duplicates. -- Don Guillett SalesAid Software "Paul" wrote in message ... Thanks...I have 35 condition in a row of 200 values...tries searching the web in vain... Paul -----Original Message----- What I sent will work to shorten the formula but maybe not all of your 35. You have 35 conditions out of ______how many. Did you see my suggestion for using < (not equal to) =SUMPRODUCT(($A$1:$a$5000="Apples")* ($B$1:$B$5000<"Really Bad")) or -- Don Guillett SalesAid Software "Paul" wrote in message ... Hi, This is a repost...Thanks Don and Julie but solutions did not work... I'm trying to count with multiple criterias (I'm only showing 3...but I have 35 criterias...Below is my formula...when I put all of my criterias...i have a "Formula too long" message...Please help...I dont want Pivot table because I want dynamic data...Thanks. Paul =SUMPRODUCT(($A$1:$A$5000="Apples")* (($B$1:$B$5000="Good") + ($B$1:$B$5000="Very Good")+ ($B$1:$B$5000="Bad"))) Should give me 3 as a results A B Apples Good Apples Very good Oranges Good Oranges Very good Oranges Very good Apples Super Apples Bad . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| http://CannotDeleteFile.net - Cannot Delete File? Try Long Path ToolFilename is too long? Computer Complaining Your Filename Is Too Long? TheLong Path Tool Can Help While most people can go about their businessblissfully unaware of the Windo | Excel Discussion (Misc queries) | |||
| Long Long Long Nested If Function | Excel Discussion (Misc queries) | |||
| Clearing cells takes long, long time | Excel Discussion (Misc queries) | |||
| Long formula | Excel Worksheet Functions | |||
| formula too long | Excel Worksheet Functions | |||
Hybrid Mode
