Sumproduct - Return value as Blank
 

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))))

Sumproduct - Return value as Blank
 

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),"")

--
Biff
Microsoft Excel MVP


"Lilyput" wrote in message
...
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))))

Sumproduct - Return value as Blank
 

Thanks Biff - this works perfectly! You are brilliant and the speedy response
is much appreciated!

"T. Valko" wrote:

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),"")

--
Biff
Microsoft Excel MVP


"Lilyput" wrote in message
...
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))))


.

Sumproduct - Return value as Blank
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Lilyput" wrote in message
...
Thanks Biff - this works perfectly! You are brilliant and the speedy
response
is much appreciated!

"T. Valko" wrote:

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),"")

--
Biff
Microsoft Excel MVP


"Lilyput" wrote in message
...
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))))


.