Sumproduct returning #Error

**Knocknaboula** · 03-06-2013, 02:16 PM

I have an #error from a sumproduct formula

sheet 1 col dm ( custom format) the one with the error.

=IF(C2="","",SUMPRODUCT(('EQ Summary'!$C$4:$C$5000=$C2)*('EQ Summary'!$E$4:$E$5000=1)*('EQ Summary'!$AL$4:$AL$5000)))

c2 is a general format - example data "ASYS"

Sheet - "EQ Summary"

Summary'!$C$4:$C$5000 is general format - example data "ASYS"

'EQ Summary'!$E$4:$E$5000 is general format - example data (1,2,3)

'EQ Summary'!$AL$4:$AL$5000 is general format example data "1106559". this data here is also the result of a sumproduct formula and displays correctly.

What am i doing wrong here?

**Spencer101** · 03-06-2013, 02:58 PM

You're missing an argument in the last condition OR you need to change the last * in the formula to a comma

=IF(C2="","",SUMPRODUCT(('EQ Summary'!$C$4:$C$5000=$C2)*('EQ Summary'!$E$4:$E$5000=1),('EQ Summary'!$AL$4:$AL$5000)))

**Tony Valko** · 03-06-2013, 03:04 PM

What is the exact error you're getting?

If there is text in the range 'EQ Summary'!$AL$4:$AL$5000 then you'll get a #VALUE! error.

Maybe this:

=IF(C2="","",SUMPRODUCT(--('EQ Summary'!$C$4:$C$5000=$C2),--('EQ Summary'!$E$4:$E$5000=1),'EQ Summary'!$AL$4:$AL$5000))

**Knocknaboula** · 03-06-2013, 03:06 PM

Thank you so much, i thought i was going crazy. Funny thing is that for other similar formulas it has been returning the correct value even when i was using * instead of a comma
.