Coffeetoday
New Member
- Joined
- Mar 26, 2017
- Messages
- 2
Hello all!
New to MrExcel and hoping to get answer to my question
Have a monthly sales worksheet with the dates in top row and products in first column.
I the sample of the sheet below is clear.
What I am trying to do is figure out the use of "Type" on a daily basis.
To do that I have to multiply the QTY*Shots then add the result for each "Type".
After long search and using the SUMPRODUCT, I was able to get the following 2 formulas to work.
(To sum the QTY of specific Type for Mar 1
SUMPRODUCT((Sales!F190:CT190=A3)*(B2=Sales!C192:C282)*Sales!F192:CT282)
(To Sum the number of shots for the same specific type)
SUMPRODUCT((Sales!C192:C282=B2)*Sales!D192:D282)
However, when I combine them together as below, I get #VALUE! error
=SUMPRODUCT((Sales!F190:CT190=A3)*(Sales!C192:C282=B2)*Sales!F192:CT282,(Sales!C192:C282=B2)*Sales!D192:D282)
<tbody>
</tbody>
<tbody>
</tbody>
Please help!
New to MrExcel and hoping to get answer to my question
Have a monthly sales worksheet with the dates in top row and products in first column.
I the sample of the sheet below is clear.
What I am trying to do is figure out the use of "Type" on a daily basis.
To do that I have to multiply the QTY*Shots then add the result for each "Type".
After long search and using the SUMPRODUCT, I was able to get the following 2 formulas to work.
(To sum the QTY of specific Type for Mar 1
SUMPRODUCT((Sales!F190:CT190=A3)*(B2=Sales!C192:C282)*Sales!F192:CT282)
(To Sum the number of shots for the same specific type)
SUMPRODUCT((Sales!C192:C282=B2)*Sales!D192:D282)
However, when I combine them together as below, I get #VALUE! error
=SUMPRODUCT((Sales!F190:CT190=A3)*(Sales!C192:C282=B2)*Sales!F192:CT282,(Sales!C192:C282=B2)*Sales!D192:D282)
B | C | D | E | F | G | H |
1-Mar-2017 | ||||||
Item | Type | Shots | SP | Quantity | Discount | Total |
PRD1 | TD-1 | 1 | 2 | |||
PRD2 | TD-3 | 2 | 3 | |||
PRD3 | TD-4 | 1 | 1 | |||
PRD4 | TD-4 | 2 | 0 | |||
PRD5 | TD-1 | 2 | 4 | |||
PRD6 | TD-3 | 2 | 3 |
<tbody>
</tbody>
<tbody>
</tbody>
Please help!