| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF-based SUMPRODUCT criteria
Hello, Great Oz..
Here's my formula: =SUMPRODUCT(((IF($B$1="All Offices",sheet2!$E$2:$E$500<0,sheet2!$E$2:$E$500= $B$1)))*(sheet2!$I$2:$I$500="type1")*(MONTH(sheet2 !$B$2:$B$500)=MONTH($D$1)),sheet2!$N$2:$N$500) sheet1, where this formula is found, is a little window onto sheet 2 which is a huge data table. Many cells on sheet1 use the value in sheet1:B1 along with additional criteria in a sumproduct formula for their value. I have sheet1!B1 set up as a drop down menu based on a list of the different offices, sheet1!S1:S10. I'm trying to set up an "All Offices" option, so instead of picking a single office they can look at them all together. However, this seems to entail changing the operator in the first argument of the sumproduct formula from = to < or vice versa. Not sure how to accomplish this. The closest I've come is the formula above, which doesn't error out, and works when "All Offices" has been selected in B1, but returns 0 when a single office is chosen, even when there should be a nonzero answer. Also tried: =SUMPRODUCT(((IF($B$1="All Offices",sheet2!$E$2:$E$500<0,sheet2!$E$2:$E$500& "="&$B$1)))*(sheet2!$I$2:$I$500="type1")*(MONTH(sh eet2!$B$2:$B$500)=MONTH($D$1)),sheet2!$N$2:$N$500) =SUMPRODUCT((sheet2!$E$2:$E$500(IF($B$1="All Offices",<0,=$B$1)))*(sheet2!$I$2:$I$500="type1") *(MONTH(sheet2!$B$2:$B$500)=MONTH($D$1)),sheet2!$N $2:$N$500) =SUMPRODUCT((sheet2!$E$2:$E$500=(IF($B$1="All Offices",AND(S1:S10),$B$1)))*(sheet2!$I$2:$I$500=" type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($D$1)),sh eet2!$N$2:$N$500) ....returns VALUE error =SUMPRODUCT((sheet2!$E$2:$E$500=(IF($B$1="All Offices","AND(S1:S10)",$B$1)))*(sheet2!$I$2:$I$500 ="type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($D$1)), sheet2!$N$2:$N$500) ....returns 0 =SUMPRODUCT((sheet2!$E$2:$E$500=(IF($B$1="All Offices",(AND(S1:S10)),$B$1)))*(sheet2!$I$2:$I$500 ="type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($D$1)), sheet2!$N$2:$N$500) ....returns VALUE error Any ideas? thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF-based SUMPRODUCT criteria
How about
=IF($B$1="All Offices", SUMPRODUCT((sheet2!$E$2:$E$500<0)*(sheet2!$I$2:$I $500="type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($D$ 1)),sheet2!$N$2:$N$500)), SUMPRODUCT((sheet2!$E$2:$E$500=$B$1)*(sheet2!$I$2: $I$500="type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($ D$1)),sheet2!$N$2:$N$500))) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "creativeops" wrote in message ... Hello, Great Oz.. Here's my formula: =SUMPRODUCT(((IF($B$1="All Offices",sheet2!$E$2:$E$500<0,sheet2!$E$2:$E$500= $B$1)))*(sheet2!$I$2:$I$500="type1")*(MONTH(sheet2 !$B$2:$B$500)=MONTH($D$1)),sheet2!$N$2:$N$500) sheet1, where this formula is found, is a little window onto sheet 2 which is a huge data table. Many cells on sheet1 use the value in sheet1:B1 along with additional criteria in a sumproduct formula for their value. I have sheet1!B1 set up as a drop down menu based on a list of the different offices, sheet1!S1:S10. I'm trying to set up an "All Offices" option, so instead of picking a single office they can look at them all together. However, this seems to entail changing the operator in the first argument of the sumproduct formula from = to < or vice versa. Not sure how to accomplish this. The closest I've come is the formula above, which doesn't error out, and works when "All Offices" has been selected in B1, but returns 0 when a single office is chosen, even when there should be a nonzero answer. Also tried: =SUMPRODUCT(((IF($B$1="All Offices",sheet2!$E$2:$E$500<0,sheet2!$E$2:$E$500& "="&$B$1)))*(sheet2!$I$2:$I$500="type1")*(MONTH(sh eet2!$B$2:$B$500)=MONTH($D$1)),sheet2!$N$2:$N$500) =SUMPRODUCT((sheet2!$E$2:$E$500(IF($B$1="All Offices",<0,=$B$1)))*(sheet2!$I$2:$I$500="type1") *(MONTH(sheet2!$B$2:$B$500)=MONTH($D$1)),sheet2!$N $2:$N$500) =SUMPRODUCT((sheet2!$E$2:$E$500=(IF($B$1="All Offices",AND(S1:S10),$B$1)))*(sheet2!$I$2:$I$500=" type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($D$1)),sh eet2!$N$2:$N$500) ...returns VALUE error =SUMPRODUCT((sheet2!$E$2:$E$500=(IF($B$1="All Offices","AND(S1:S10)",$B$1)))*(sheet2!$I$2:$I$500 ="type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($D$1)), sheet2!$N$2:$N$500) ...returns 0 =SUMPRODUCT((sheet2!$E$2:$E$500=(IF($B$1="All Offices",(AND(S1:S10)),$B$1)))*(sheet2!$I$2:$I$500 ="type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($D$1)), sheet2!$N$2:$N$500) ...returns VALUE error Any ideas? thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF-based SUMPRODUCT criteria
Mmm, darn, no, unfortunately that didn't work. Formula error dialog box
comes up, then just highlights the whole thing :( I almost emailed you directly Bob - you seem like the SUMPRODUCT guy! I think the IF formula has problems with equal sign in the true or false value. And maybe asterisks also. "Bob Phillips" wrote: How about =IF($B$1="All Offices", SUMPRODUCT((sheet2!$E$2:$E$500<0)*(sheet2!$I$2:$I $500="type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($D$ 1)),sheet2!$N$2:$N$500)), SUMPRODUCT((sheet2!$E$2:$E$500=$B$1)*(sheet2!$I$2: $I$500="type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($ D$1)),sheet2!$N$2:$N$500))) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "creativeops" wrote in message ... Hello, Great Oz.. Here's my formula: =SUMPRODUCT(((IF($B$1="All Offices",sheet2!$E$2:$E$500<0,sheet2!$E$2:$E$500= $B$1)))*(sheet2!$I$2:$I$500="type1")*(MONTH(sheet2 !$B$2:$B$500)=MONTH($D$1)),sheet2!$N$2:$N$500) sheet1, where this formula is found, is a little window onto sheet 2 which is a huge data table. Many cells on sheet1 use the value in sheet1:B1 along with additional criteria in a sumproduct formula for their value. I have sheet1!B1 set up as a drop down menu based on a list of the different offices, sheet1!S1:S10. I'm trying to set up an "All Offices" option, so instead of picking a single office they can look at them all together. However, this seems to entail changing the operator in the first argument of the sumproduct formula from = to < or vice versa. Not sure how to accomplish this. The closest I've come is the formula above, which doesn't error out, and works when "All Offices" has been selected in B1, but returns 0 when a single office is chosen, even when there should be a nonzero answer. Also tried: =SUMPRODUCT(((IF($B$1="All Offices",sheet2!$E$2:$E$500<0,sheet2!$E$2:$E$500& "="&$B$1)))*(sheet2!$I$2:$I$500="type1")*(MONTH(sh eet2!$B$2:$B$500)=MONTH($D$1)),sheet2!$N$2:$N$500) =SUMPRODUCT((sheet2!$E$2:$E$500(IF($B$1="All Offices",<0,=$B$1)))*(sheet2!$I$2:$I$500="type1") *(MONTH(sheet2!$B$2:$B$500)=MONTH($D$1)),sheet2!$N $2:$N$500) =SUMPRODUCT((sheet2!$E$2:$E$500=(IF($B$1="All Offices",AND(S1:S10),$B$1)))*(sheet2!$I$2:$I$500=" type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($D$1)),sh eet2!$N$2:$N$500) ...returns VALUE error =SUMPRODUCT((sheet2!$E$2:$E$500=(IF($B$1="All Offices","AND(S1:S10)",$B$1)))*(sheet2!$I$2:$I$500 ="type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($D$1)), sheet2!$N$2:$N$500) ...returns 0 =SUMPRODUCT((sheet2!$E$2:$E$500=(IF($B$1="All Offices",(AND(S1:S10)),$B$1)))*(sheet2!$I$2:$I$500 ="type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($D$1)), sheet2!$N$2:$N$500) ...returns VALUE error Any ideas? thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF-based SUMPRODUCT criteria
An erroneous placed bracket was the problem
=IF($B$1="All Offices", SUMPRODUCT((Sheet2!$E$2:$E$500<0)*(Sheet2!$I$2:$I $500="type1")*(MONTH(Sheet2!$B$2:$B$500)=MONTH($D$ 1)),Sheet2!$N$2:$N$500), SUMPRODUCT((Sheet2!$E$2:$E$500=$B$1)*(Sheet2!$I$2: $I$500="type1")*(MONTH(Sheet2!$B$2:$B$500)=MONTH($ D$1)),Sheet2!$N$2:$N$500)) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "creativeops" wrote in message ... Mmm, darn, no, unfortunately that didn't work. Formula error dialog box comes up, then just highlights the whole thing :( I almost emailed you directly Bob - you seem like the SUMPRODUCT guy! I think the IF formula has problems with equal sign in the true or false value. And maybe asterisks also. "Bob Phillips" wrote: How about =IF($B$1="All Offices", SUMPRODUCT((sheet2!$E$2:$E$500<0)*(sheet2!$I$2:$I $500="type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($D$ 1)),sheet2!$N$2:$N$500)), SUMPRODUCT((sheet2!$E$2:$E$500=$B$1)*(sheet2!$I$2: $I$500="type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($ D$1)),sheet2!$N$2:$N$500))) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "creativeops" wrote in message ... Hello, Great Oz.. Here's my formula: =SUMPRODUCT(((IF($B$1="All Offices",sheet2!$E$2:$E$500<0,sheet2!$E$2:$E$500= $B$1)))*(sheet2!$I$2:$I$500="type1")*(MONTH(sheet2 !$B$2:$B$500)=MONTH($D$1)),sheet2!$N$2:$N$500) sheet1, where this formula is found, is a little window onto sheet 2 which is a huge data table. Many cells on sheet1 use the value in sheet1:B1 along with additional criteria in a sumproduct formula for their value. I have sheet1!B1 set up as a drop down menu based on a list of the different offices, sheet1!S1:S10. I'm trying to set up an "All Offices" option, so instead of picking a single office they can look at them all together. However, this seems to entail changing the operator in the first argument of the sumproduct formula from = to < or vice versa. Not sure how to accomplish this. The closest I've come is the formula above, which doesn't error out, and works when "All Offices" has been selected in B1, but returns 0 when a single office is chosen, even when there should be a nonzero answer. Also tried: =SUMPRODUCT(((IF($B$1="All Offices",sheet2!$E$2:$E$500<0,sheet2!$E$2:$E$500& "="&$B$1)))*(sheet2!$I$2:$I$500="type1")*(MONTH(sh eet2!$B$2:$B$500)=MONTH($D$1)),sheet2!$N$2:$N$500) =SUMPRODUCT((sheet2!$E$2:$E$500(IF($B$1="All Offices",<0,=$B$1)))*(sheet2!$I$2:$I$500="type1") *(MONTH(sheet2!$B$2:$B$500)=MONTH($D$1)),sheet2!$N $2:$N$500) =SUMPRODUCT((sheet2!$E$2:$E$500=(IF($B$1="All Offices",AND(S1:S10),$B$1)))*(sheet2!$I$2:$I$500=" type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($D$1)),sh eet2!$N$2:$N$500) ...returns VALUE error =SUMPRODUCT((sheet2!$E$2:$E$500=(IF($B$1="All Offices","AND(S1:S10)",$B$1)))*(sheet2!$I$2:$I$500 ="type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($D$1)), sheet2!$N$2:$N$500) ...returns 0 =SUMPRODUCT((sheet2!$E$2:$E$500=(IF($B$1="All Offices",(AND(S1:S10)),$B$1)))*(sheet2!$I$2:$I$500 ="type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($D$1)), sheet2!$N$2:$N$500) ...returns VALUE error Any ideas? thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF-based SUMPRODUCT criteria
that did it! thanks so much Bob
"Bob Phillips" wrote: An erroneous placed bracket was the problem =IF($B$1="All Offices", SUMPRODUCT((Sheet2!$E$2:$E$500<0)*(Sheet2!$I$2:$I $500="type1")*(MONTH(Sheet2!$B$2:$B$500)=MONTH($D$ 1)),Sheet2!$N$2:$N$500), SUMPRODUCT((Sheet2!$E$2:$E$500=$B$1)*(Sheet2!$I$2: $I$500="type1")*(MONTH(Sheet2!$B$2:$B$500)=MONTH($ D$1)),Sheet2!$N$2:$N$500)) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "creativeops" wrote in message ... Mmm, darn, no, unfortunately that didn't work. Formula error dialog box comes up, then just highlights the whole thing :( I almost emailed you directly Bob - you seem like the SUMPRODUCT guy! I think the IF formula has problems with equal sign in the true or false value. And maybe asterisks also. "Bob Phillips" wrote: How about =IF($B$1="All Offices", SUMPRODUCT((sheet2!$E$2:$E$500<0)*(sheet2!$I$2:$I $500="type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($D$ 1)),sheet2!$N$2:$N$500)), SUMPRODUCT((sheet2!$E$2:$E$500=$B$1)*(sheet2!$I$2: $I$500="type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($ D$1)),sheet2!$N$2:$N$500))) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "creativeops" wrote in message ... Hello, Great Oz.. Here's my formula: =SUMPRODUCT(((IF($B$1="All Offices",sheet2!$E$2:$E$500<0,sheet2!$E$2:$E$500= $B$1)))*(sheet2!$I$2:$I$500="type1")*(MONTH(sheet2 !$B$2:$B$500)=MONTH($D$1)),sheet2!$N$2:$N$500) sheet1, where this formula is found, is a little window onto sheet 2 which is a huge data table. Many cells on sheet1 use the value in sheet1:B1 along with additional criteria in a sumproduct formula for their value. I have sheet1!B1 set up as a drop down menu based on a list of the different offices, sheet1!S1:S10. I'm trying to set up an "All Offices" option, so instead of picking a single office they can look at them all together. However, this seems to entail changing the operator in the first argument of the sumproduct formula from = to < or vice versa. Not sure how to accomplish this. The closest I've come is the formula above, which doesn't error out, and works when "All Offices" has been selected in B1, but returns 0 when a single office is chosen, even when there should be a nonzero answer. Also tried: =SUMPRODUCT(((IF($B$1="All Offices",sheet2!$E$2:$E$500<0,sheet2!$E$2:$E$500& "="&$B$1)))*(sheet2!$I$2:$I$500="type1")*(MONTH(sh eet2!$B$2:$B$500)=MONTH($D$1)),sheet2!$N$2:$N$500) =SUMPRODUCT((sheet2!$E$2:$E$500(IF($B$1="All Offices",<0,=$B$1)))*(sheet2!$I$2:$I$500="type1") *(MONTH(sheet2!$B$2:$B$500)=MONTH($D$1)),sheet2!$N $2:$N$500) =SUMPRODUCT((sheet2!$E$2:$E$500=(IF($B$1="All Offices",AND(S1:S10),$B$1)))*(sheet2!$I$2:$I$500=" type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($D$1)),sh eet2!$N$2:$N$500) ...returns VALUE error =SUMPRODUCT((sheet2!$E$2:$E$500=(IF($B$1="All Offices","AND(S1:S10)",$B$1)))*(sheet2!$I$2:$I$500 ="type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($D$1)), sheet2!$N$2:$N$500) ...returns 0 =SUMPRODUCT((sheet2!$E$2:$E$500=(IF($B$1="All Offices",(AND(S1:S10)),$B$1)))*(sheet2!$I$2:$I$500 ="type1")*(MONTH(sheet2!$B$2:$B$500)=MONTH($D$1)), sheet2!$N$2:$N$500) ...returns VALUE error Any ideas? thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup function based on 3 criteria | Excel Discussion (Misc queries) | |||
| SUMPRODUCT, two criteria (date and product type) | Excel Worksheet Functions | |||
| Counting and Suming based on multiple criteria | Excel Worksheet Functions | |||
| Calculating an average based on 2 and 3 criteria | Excel Worksheet Functions | |||
| Returning Results Based on Two Criteria | Excel Worksheet Functions | |||
Linear Mode
