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,
Bookmarks