Sumproduct with calculated column

[Charles L. Snyder]

Hi

I am trying to use the following formula in the worksheet 'Reports':

=SUMPRODUCT((Operations!B3:B2312>(--("2005/12/31")))*(Operations!L3:L2312))

but,

the values in Operations!L3:L2312 are actually just filldown
calculations:

=VLOOKUP(G2202,'RVU Lookup'!A901:B8111,2,FALSE)

and therefore the sumproduct doesn't work. I could copy the L column,
paste special the values, run the sumproduct, and it should work:

- isn't there a way to do this with VBA or at least an easier automated
solution?

thanks in advance

CLS

[[email protected]]

Hi Charles,

Your initial approach should work. It doesn't matter that the values
in column L are returned by a formula. Does the SUMPRODUCT return an
error or just give you the wrong answer?

I see that you are using a relative reference in your VLOOKUP. This
may be how you want it but it has caught me out in the past

Rgds,
Andrew


Charles L. Snyder wrote:
> Hi
>
> I am trying to use the following formula in the worksheet 'Reports':
>
> =SUMPRODUCT((Operations!B3:B2312>(--("2005/12/31")))*(Operations!L3:L2312))
>
> but,
>
> the values in Operations!L3:L2312 are actually just filldown
> calculations:
>
> =VLOOKUP(G2202,'RVU Lookup'!A901:B8111,2,FALSE)
>
> and therefore the sumproduct doesn't work. I could copy the L column,
> paste special the values, run the sumproduct, and it should work:
>
> - isn't there a way to do this with VBA or at least an easier automated
> solution?
>
> thanks in advance
>
> CLS

[Charles L. Snyder]

I just grabbed a copy of the VLOOKUP formula from one of the cells to
put in my post - hence therelative VLOOKUP reference.

Perhaps the problem is that a few of the cells in column L (the
calculated column) have a value of #N/A, since the calculation formula
found an empty lookup for that value?

Thanks

Charles

[[email protected]]

You could get around this by replacing the lookup function with:
=IF(ISNA(VLOOKUP(G2202,'RVU
Lookup'!A901:B8111,2,FALSE)),0,VLOOKUP(G2202,'RVU
Lookup'!A901:B8111,2,FALSE))

This will now return 0 if the value is not found instead of #N/A
(assuming that is acceptable in your table)

Andrew

[JMB]

One possible solution to exclude the error values:

=SUMPRODUCT((Operations!B3:B2312>(--("2005/12/31")))*(IF(ISNUMBER(Operations!L3:L2312),Operations!L3:L2312,0)))

Enter with Control+Shift+Enter.

"Charles L. Snyder" wrote:

> I just grabbed a copy of the VLOOKUP formula from one of the cells to
> put in my post - hence therelative VLOOKUP reference.
>
> Perhaps the problem is that a few of the cells in column L (the
> calculated column) have a value of #N/A, since the calculation formula
> found an empty lookup for that value?
>
> Thanks
>
> Charles
>
>

[JMB]

You could also use an IF statement w/Vlookup to return a 0 instead of an error:

=IF(ISNA(VLOOKUP(G2202,'RVU
Lookup'!A901:B8111,2,FALSE)),0,VLOOKUP(G2202,'RVU Lookup'!A901:B8111,2,FALSE))

Then you would not have to modify your SUMPRODUCT function.


"Charles L. Snyder" wrote:

> I just grabbed a copy of the VLOOKUP formula from one of the cells to
> put in my post - hence therelative VLOOKUP reference.
>
> Perhaps the problem is that a few of the cells in column L (the
> calculated column) have a value of #N/A, since the calculation formula
> found an empty lookup for that value?
>
> Thanks
>
> Charles
>
>

[Charles L. Snyder]

On 2006-01-24 23:54:02 -0600, JMB <[email protected]> said:

> You could also use an IF statement w/Vlookup to return a 0 instead of an error:
>
> =IF(ISNA(VLOOKUP(G2202,'RVU
> Lookup'!A901:B8111,2,FALSE)),0,VLOOKUP(G2202,'RVU
> Lookup'!A901:B8111,2,FALSE))
>
> Then you would not have to modify your SUMPRODUCT function.
>
>
> "Charles L. Snyder" wrote:
>
>> I just grabbed a copy of the VLOOKUP formula from one of the cells to
>> put in my post - hence therelative VLOOKUP reference.
>>
>> Perhaps the problem is that a few of the cells in column L (the
>> calculated column) have a value of #N/A, since the calculation formula
>> found an empty lookup for that value?
>>
>> Thanks
>>
>> Charles

Thanks for the help - it works perfectly now !
CLS