Sum Product with Multiple Conditions

[mhordern]

Hello,

I wish to calculate the sum product (quantity*price) of the following information:

A B C D E
1 When Sold Product Who Bought It Quantity Price
2 Q3 11 tomatoes martha 8.694 19
3 Q3 11 apples john 7.308 87
4 Q3 11 tomatoes Martha 7.308 54
5 Q3 11 tomatoes john 7.686 25
6 Q3 11 apples Martha 3.654 26
7 Q4 11 tomatoes martha 14.616 29
8 Q4 11 apples john 3.906 47
9 Q4 11 apples john 3.654 31
10 Q4 11 tomatoes martha 7.308 42

My problem apears when i want to classify the sum product given criteria as (when sold = Q3-11, product sold = tomatoes, person who bought it = martha).

I have used the following formula but it does not calculate the sumproduct correctly:

IF((COUNTIFS($C$2:$C$10,"="&"Martha",$A$2:$A$10,"="&"Q311",$B$2:$B$10,"="&"tomatoes")=0)," ",((SUMIFS($D$2:$D$10,$C$2:$C$10,"="&"Martha",$A$2:$A$10,"="&"Q311",$B$2:$B$10,"="&"tomatoes")*SUMIFS($E$2:$E$10,$C$2:$C$10,"="&"Martha",$A$2:$A$10,"="&"Q311",$B$2:$B$10,"="&"tomatoes"))/SUMIFS($D$2:$D$10,$C$2:$C$10,"="&"Martha",$A$2:$A$10,"="&"Q311",$B$2:$B$10,"="&"tomatoes"))/COUNTIFS($C$2:$C$10,"="&"Martha",$A$2:$A$10,"="&"Q311",$B$2:$B$10,"="&"tomatoes")) = 36,50

When a normal sumproduct ((SUMPRODUCT(D2,E2)+SUMPRODUCT(D4,E4))/SUM(D2,D4)) would give me 34,98

Can you please help with a formula whcih can calculate the sum product given multiple criteria

Regards,

[jeffreybrown]

(when sold = Q3-11, product sold = tomatoes, person who bought it = martha).

Instead of a formula, can you just tell us what the answer is for the criteria you state above and we'll go from there?

[mhordern]

For the criteria stated above it should look for sum product of prices and quatntiy for all tomatoes martha bought during Q3-11 = 34,98

[mhordern]

to avoid confusion the formula is: (SUMIFS($D$2:$D$10,$C$2:$C$10,"="&"Martha",$A$2:$A$10,"="&"Q311",$B$2:$B$10,"="&"tomatoes")*SUMIF S($E$2:$E$10,$C$2:$C$10,"="&"Martha",$A$2:$A$10,"="&"Q311",$B$2:$B$10,"="&"tomatoes"))/SUMIFS($D$2:$D$10,$C$2:$C$10,"="&"Martha",$A$2:$A$10,"="&"Q311",$B$2:$B$10,"="&"tomatoes"))/COUNTIFS($C$2:$C$10,"="&"Martha",$A$2:$A$10,"="&"Q311",$B$2:$B$10,"="&"tomatoes")

[mhordern]

given the criteria, i want to sum the multiplication of the quantity times the price and divide by the quantity.

[mhordern]

the formula i have posted sums the quantities multipies by the sum of prices, divides by the sum of quantities and then i divid the whole thing by the criteria used (countif)... but this is wrong and i dont know an other way to have sum product with multple criteria....

[daddylonglegs]

In your example you show spaces between quarter and year like Q3 11......but you use Q311 (without space) in the formulas (and also mention Q3-11). Assuming Q311 is correct then try this formula

=IFERROR(SUMPRODUCT((A2:A10="Q311")*(B2:B10="tomatoes")*(C2:C10="Martha"),D2:D10,E2:E10)/SUMIFS(D2:D10,A2:A10,"Q311",B2:B10,"tomatoes",C2:C10,"Martha"),"")

[justkiding1593]

=IFERROR(SUMPRODUCT((A2:A10="Q311")*(B2:B10="tomatoes")*(C2:C10="Martha"),D2:D10,E2:E10)/SUMIFS(D2:D10,A2:A10,"Q311",B2:B10,"tomatoes",C2:C10,"Martha"),"")

I am new to forums, but here it goes..... I have looked everywhere on how to use this formula. No matter what I do, replace commas with asteriks, visa versa, you name it, I've tried it. Here is my latest attempt and I don't see my issue:

a c h i o
group# ind# total Percent include Expected Answer
11111 1 73 .95 Yes (73*.95)+(41*.98)/(73+41)
11111 2 41 .98 Yes (73*.95)+(41*.98)/(73+41)
11111 3 4 .10 No (73*.95)+(41*.98)/(73+41)
22222 1 55 .55 Yes (55*.55)/(55)
33333 1 No (60*1)/(60)
33333 2 60 1 Yes (60*1)/(60)


Here is my formula:
=SUMPRODUCT(($A:$A=$A2)*(O:O="Yes"),H:H,I:I)/SUMIFS(H:H,A:A,A2,O:O,"Yes")

I want to be able to include in the calculations only those that meet the criteria, but the answer needs to also show up for the individuals in the group that don't meet the criteria....
I used the information in the posts from this forum and UGGGGG! Can anyone help?

[ConneXionLost]

justkiding1593,

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.