Sumproduct - Return value as Blank

  • Thread starter Thread starter Lilyput
  • Start date Start date
L

Lilyput

Hi
I am using the sumproduct formula below to pull information from one work
sheet to another. However some of the values in the range ranking order
E5:E38 are blank. The rest are 0% to 100%. I need to get my formula to return
blank instead of Zero unless the value in col E actually is Zero. I have
tried IF on it's own as well however I am not getting result I am looking
for. Any help appreciated!

Formula - =IF(ISBLANK(SUMPRODUCT(--('RANKING ORDER'!A$8:A$35=A8)*(--'RANKING
ORDER'!E$8:E$35))),"",(SUMPRODUCT(--('RANKING ORDER'!A$8:A$35=A8)*(--'RANKING
ORDER'!E$8:E$35))))
 
Try something like this...

All on one line.

=IF(SUMPRODUCT(--('Ranking Order'!A$8:A$35=A8),
--('Ranking Order'!E$8:E$35<>"")),
SUMPRODUCT(--('Ranking Order'!A$8:A$35=A8),
'Ranking Order'!E$8:E$35),"")
 
Thanks Biff - this works perfectly! You are brilliant and the speedy response
is much appreciated!
 
Back
Top