Conditional Sum Wizard - "The formula you are building is too long" error

[Louise Rostron]

Hi,

I am trying to use the conditional sum wizard (which I use all the time normally with no problems!) but on this occassion I keep getting an error message:

"The formula you are building is too long. Please specify fewer conditions"

This comes up when I have only got 2 conditions in place! It appears at the point where I have just set the conditions and am clicking Next at the end of step 2.

I have tried removing one of the conditions and one of them is fine so I at least know which condition it is. The column that this condition refers to has just numbers in it - not even formulae just typed in.

My data is quite large (26950 rows) but other conditional sums that I have on this worksheet are working fine so I can't see why that would matter.

Has anyone seen this error before and if so do you know how to fix it?

Thanks,

Louise

 

[Trevor G]

What are the condition and what formula have you tried?

 

[Louise Rostron]

Hi Trevor,

I have tried to manually change one that already works by amending the column references to column E instead of column D (remembering to press CTRL, SHIFT and ENTER to preserve the Array) I get a #N/A - but there is genuine data that it should be adding up. Here is the formula:

{=SUM(IF('Raw data - Items Dispensed'!$E$2:$E$45353=34,IF('Raw data - Items Dispensed'!$C$2:$C$45353=4,'Raw data - Items Dispensed'!$M$2:$M$45353,0),0))}

When I try to re-create the conditional sum from scratch it is the condition where I am looking up 34 in column E that is causing the error message about formula being too long.

Column E has 37 different values in it - is this too many for a conditional sum to cope with? The reason that I ask is that if I take just the first coulple of hundred rows of my data then there are only 3 different values in column E and the conditional sum works fine.

I have gone as far as row 45353 to allow for future data to be added (and this has worked fine on other conditional sums in this worksheet)

Any ideas?

Thanks,

Louise

 

[Aladin Akyurek]

Dispense those zero's...

Control+shift+enter:

=SUM(IF('Raw data - Items Dispensed'!$E$2:$E$45353=34,IF('Raw data - Items Dispensed'!$C$2:$C$45353=4,'Raw data - Items Dispensed'!$M$2:$M$45353)))

 

[Trevor G]

Look to remove the zeros and see that helps

Control+shift+enter:

=SUM(IF('Raw data - Items Dispensed'!$E$2:$E$45353=34,IF('Raw data - Items Dispensed'!$C$2:$C$45353=4,'Raw data - Items Dispensed'!$M$2:$M$45353)))

 

[Louise Rostron]

Have tried this but still getting #N/A returned

 

[RoryA]

Do you have any error values in column E, C or M?

 

[Louise Rostron]

That's it! Thanks Rory, the data had #N/A against some rows (someone has obviously used V-Look up somewhere along the way and then done an edit paste special to hide it!). I have removed those and it is now working fine.

Thanks for that.

Louise