Sumproduct with calculated column
 

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

Sumproduct with calculated column
 

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

Sumproduct with calculated column
 

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

Sumproduct with calculated column
 

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

Sumproduct with calculated column
 

One possible solution to exclude the error values:

=SUMPRODUCT((Operations!B3:B2312(--("2005/12/31")))*(IF(ISNUMBER(Operations!L3:L2312),Operation s!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

Sumproduct with calculated column
 

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

Sumproduct with calculated column
 

On 2006-01-24 23:54:02 -0600, JMB 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