Excel limitation or formula (sumproduct with multiple criteria) #Value problem?

[mambonumber5]

EDIT: I tried changing SUMPRODUCT to just SUM and that works. How do I make SUMPRODUCT work to get the output I need?


Hi all,

I've got a long SUMPRODUCT formula to search through a big database and sum the range of cells that match all criteria. I've checked all individual components of my formula and they do seem to work; yet, when I put them together in one SUMPRODUCT(--(),--()) statement, I get a #Value error. Could this be because I've reached some Excel limitation for calculating this many arrays together; or is it a problem with my formula that I can't seem to find? Is there a way to simplify this formula?

The formula is:

{=ROUNDUP(SUMPRODUCT(--(Inputs.Setup!$B$3:$B$27458=INDEX(Budget!$C$4:$HM$4,,MATCH(YEAR(D45)&MONTH(D45),Budget!$C$2:$HM$2&Budget!$C$3:$HM$3,0))),--ISNUMBER(MATCH(Inputs.Setup!$D$3:$D$27458,VALUE(TRIM(MID(SUBSTITUTE(E45,",",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(E45)-LEN(SUBSTITUTE(E45,",",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(E45)-LEN(SUBSTITUTE(E45,",",""))+1)))=1),99))),0)),--(LEFT(Inputs.Setup!$H$3:$H$27458,1)="P"),--(Inputs.Setup!$J$3:$J$27458={"remote","direct"}),--(Inputs.Setup!$K$3:$K$27458="yes"),Inputs.Setup!$N$3:$N$27458*Workshop_attrition^RIGHT(Inputs.Setup!$H$3:$H$27458,1)*Mentoring_attrition^RIGHT(Inputs.Setup!$H$3:$H$27458,1)),0)}

Any clue? Thanks!

 

[mambonumber5]

EDIT: I changed
--(Inputs.Setup!$J$3:$J$27458={"remote","direct"})
to
--((Inputs.Setup!$J$3:$J$27458="remote")+(Inputs.Setup!$J$3:$J$27458="direct"))
and now the code seems to work. fantastic, I should post here more often :d



Ok, I think I found what's causing the error:

{=SUMPRODUCT(--(Inputs.Setup!$J$3:$J$27458={"remote","direct"}))}

This line seems to work fine on it's won but it won't work in combination with the other criteria arrays. If I put just "remote"or just "direct" it will work. Is there a way to avoid repeating the giant formula for each by using them together in just one statement? The logic is that If Inputs.Setup!$J$3:$J$27458 = OR ("remote","direct"), it should give 1.