Solved

# Sumproduct Formula

Posted on 2014-03-21
Medium Priority
264 Views
This formula gives me the correct answer, but is there a way to simplify it so that I do not have to have 2 SUMPRODUCTS?

=+SUMPRODUCT((A10=Data!\$A\$2:\$A\$5000)*(F\$3=Data!\$L\$2:\$L\$5000)*(Data!\$E\$2:\$E\$5000)*(Data!\$F\$2:\$F\$5000))/+SUMPRODUCT((A10=Data!\$A\$2:\$A\$5000)*(F\$3=Data!\$L\$2:\$L\$5000)*(Data!\$F\$2:\$F\$5000))

Thanks
0
Question by:mak345
• 2

LVL 39

Expert Comment

ID: 39945703
I believe adding 1 to the first formula will give you the same result

=SUMPRODUCT((A10=Data!\$A\$2:\$A\$5000)*(F\$3=Data!\$L\$2:\$L\$5000)*(1+Data!\$E\$2:\$E\$5000)*(Data!\$F\$2:\$F\$5000))

Thomas
0

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 39946686
I don't believe that will give the same result, Thomas, the second SUMPRODUCT is used as the denominator in a division not an addition.

mak345,

The answer to your question is: No, there's no way to simplify in any significant way - this is the standard way to get a "weighted average" with 2 conditions. If it was me I would tidy up the syntax a little....and if you want you can use SUMIFS in place of the 2nd SUMPRODUCT (assuming you have excel 2007 or later) which will be very marginally more efficient, i.e.

=SUMPRODUCT((A10=data!\$A\$2:\$A\$5000)*(F\$3=data!\$L\$2:\$L\$5000),data!\$E\$2:\$E\$5000,data!\$F\$2:\$F\$5000)/SUMIFS(data!\$F\$2:\$F\$5000,data!\$A\$2:\$A\$5000,A10,data!\$L\$2:\$L\$5000,F\$3)

...but I'd say that's just "tinkering around the edges", your current formula is almost as good as it can be

regards, barry
0

LVL 39

Expert Comment

ID: 39946688
Thanks barry, I obviously missed a bar there.
0

Author Closing Comment

ID: 39946715
Thank you!
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.