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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Ray from NZ" wrote:

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