Conditional sum resulting in #n/a

**Meatwad** · 01-02-2010, 06:26 AM

Hi there,

I have looked for a solution for this elsewhere but has been unable to find anything.

The problem: users must paste in data sets of varying size, anything from 200 to 5000 rows of data. I'm looking to run a conditional sum formula that can accommodate these varying ranges.

The following results in an #N/A error due to empty cells:

{=SUM(IF('Sheet1'!$D$2:$D$5000=A2;'Sheet1'!$I$2:$I$5000;0))}

I assume something can be built on to fix this issue. Question is just what.

Solutions appreciated.

M/W

**zbor** · 01-02-2010, 06:31 AM

Did you type brackets {} manually?
Because you can't do that. You need to comfirm formula with ctrl+shift+enter

Also, isnt that just =SUMIF(Sheet1!$D$2:$D$5000;A2;Sheet1!$I$2:$I$5000)?

**DonkeyOte** · 01-02-2010, 06:38 AM

"empty cells" would not generate the #N/A error.

It sounds as though you have underlying error values in either of or indeed both of the ranges referenced ... on which basis: what does the below return ?

=SUM(IF(NOT(ISNA(Sheet1!$D$2:$D$5000))*ISNUMBER(Sheet1!$I$2:$I$5000);(Sheet1!$D$2:$D$5000=$A2)*Sheet1!$I$2:$I$5000))
confirmed with CTRL + SHIFT + ENTER