Prev: Programmable Column Sequencing?
Next: Two values not adding correctly
From: Lilyput on 25 May 2010 17:41
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))))
From: T. Valko on 25 May 2010 21:14
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" <Lilyput(a)discussions.microsoft.com> wrote in message
news:35B44D57-B1D6-4D09-B837-7FD56A31CF1A(a)microsoft.com...
> 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))))


From: Lilyput on 27 May 2010 18:04
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" <Lilyput(a)discussions.microsoft.com> wrote in message
> news:35B44D57-B1D6-4D09-B837-7FD56A31CF1A(a)microsoft.com...
> > 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))))
>
>
> .
>
From: T. Valko on 27 May 2010 21:52
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Lilyput" <Lilyput(a)discussions.microsoft.com> wrote in message
news:321776AD-62F0-47FA-A14E-FDA4964C1A7B(a)microsoft.com...
> 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" <Lilyput(a)discussions.microsoft.com> wrote in message
>> news:35B44D57-B1D6-4D09-B837-7FD56A31CF1A(a)microsoft.com...
>> > 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))))
>>
>>
>> .
>>


 | 
Pages: 1
Prev: Programmable Column Sequencing?
Next: Two values not adding correctly