Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct and if statements
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX / MATCH with SUMIF/COUNTIF/SUMPRODUCT Statements? | Excel Worksheet Functions | |||
Why this SUMPRODUCT statements returns zero? | Excel Worksheet Functions | |||
if statements in sumproduct formula | Excel Worksheet Functions | |||
IF Statements (Mutliple Statements) | Excel Worksheet Functions | |||
SumIf or SumProduct or If statements | Excel Worksheet Functions |