Sumproduct and if statements

[Ray from NZ]

Hi,
Thanks for looking at my XL problem. I have a spreadsheet below which I
want to use to give a basic sales forecast.

In Col G I have products. I also have a Status of "On hold, Won, Lost and
Pending" in column W.

What I want to do for each product is total them (count as a sale) in each
month provided that the status is either Won or Pending. I don't want to
count if they are lost or on-hold.

Here is what doesn't work !!!

=SUMPRODUCT(J5:J27,S5:S27,"=if(G5:G27=G50,1,0)","=if(w5:w27="Pending"OR"Won",1,0)")

Example.

Product Value Sep Oct Nov Dec Status
A 55,000.00 1 On Hold
B 45,000 1 Pending
A 55,000 1 Pending
B 55,000 1 On Hold
A 55,000 1 1 Won
C 10,000 1 Pending
C 100,000 1 Pending

Totals
A 55000 55000
B 100000
C 10000 110000 100000
D

Any help would be greatly appreciated.

- Ray

 

[Luke M]

I'll assume your data is on "Sheet 1". Placing this into cell B2 of your
totals tab...

=SUMPRODUCT(--('Sheet 1'!$A$2:$A$100=$A2),'Sheet 1'!C$2:C$100,'Sheet
1'!$B$2:$B$100,('Sheet 1'$W$2:$W$100="Won")+('Sheet 1'!$W$2:$W$100="Pending"))

This formula has product in column A, value in column B, the month you are
totaling on column C, and Status in column W.

Adjust column references and range sizes as appropriate.

 

[Eduardo]

Hi,
I assume your summary is in sheet1 and the data in sheet2

=SUMPRODUCT(--($A2=sheet2!$g$2:$g$1000),--(sheet2!I$1:I$1000=1),sheet2!$h$2:$h$1000,(sheet2!$w$2:$w$1000="Won")+($w$2:$w$1000="Pending"))

Column I is where you have 1 or blank
Column H, where you have the amounts

A2 is where you have your product A in the summary sheet

if this helps please click yes thanks

 

[Eduardo]

Opps I missed something use this formula

=SUMPRODUCT(--($A2=sheet2!$g$2:$g$1000),--(sheet2!I$1:I$1000=1),sheet2!$h$2:$h$1000,(sheet2!$w$2:$w$1000="Won")+(sheet2!$w$2:$w$1000="Pending"))

 

[Joachim]

Hallo Ray,

Sumproduct() does normally recognize both logical values as 0 while basic
calculations accept true/false as 1/0.
Assuming your product in column G, value in H, first month in I, Status in W
and your total product references in column A starting with the first
reference in A2 I suggest:

=SUM(($G$2:$G$8=$A2)*$H$2:$H$8*I$2:I$8*(($W$2:$W$8="Pending")+($W$2:$W$8="Won")))

Of course you need to enter this as matrix formula and modify the ranges as
required.