IF-based SUMPRODUCT criteria

[Guest]

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(sheet2!$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)),sheet2!$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!

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)