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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct and if statements
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct and if statements
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 "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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct and if statements
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" )) "Eduardo" wrote: 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 "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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct and if statements
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. -- Regards Joachim "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 |
Reply |
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 |