I feel sure there's a SUMPRODUCT formula in this somewhere..

[twaccess]

When it came to coming up with this formula for a slightly complex
summing and sharing out proportionately a value between several other
subsets, I just knew I had to use a SUMPRODUCT formula.

However, this sublime function is still proving difficult to completely
get my head around, so I did it the hard way like this.

=ROUND(VLOOKUP(A111,'Invoice
List'!A,4,FALSE)/SUMIF(A:E,A111,E:E)*E111,2)

What it does, is take a value on an invoice which can have multiple
job codes and divides it up between the job codes proportionately.

So, can a SUMPRODUCT formula do the same thing ?

Terry

 

[Jerry W. Lewis]

SUMPRODUCT cannot refer to an entire column, so you would have to reduce
the ranges. If you want to replace SUMIF with SUMPRODUCT, it is not
clear that there would be any advantage, since you only have a single
condition, and presumably have a working furmula. If you want to
replace the entire formula with SUMPRODUCT, then you need to say more
about what data is where on the worksheet.

Jerry