Formula too long

[Paul]

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

 

[Jack Schitt]

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.

 

[Don Guillett]

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

 

[Paul]

Thanks...I have 35 condition in a row of 200
values...tries searching the web in vain...

Paul

 

[Don Guillett]

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.

 

[JulieD]

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