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

 

[hooksie2]

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]

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

 

[hooksie2]

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

 

[Guest]

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.

 

[Guest]

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]

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.

Thanks for the help - it works perfectly now !
CLS