SUM if with 3 criteria

[Guest]

For some reason the
--(Input!$K$2:$K$1000=$J$18),
Criteria doesn't work?

I've used the following formula:

=SUMPRODUCT(--(Input!$G$2:$G$1000=$J$19),--(Input!$K$2:$K$1000=$J$18),--(Input!$A$2:$A$1000=A21)*Input!$E$2:$E$1000)

Could it be that the cells in Input!K2:K1000 column have formulas in them?

Help Please?

 

[JulieD]

Hi Alex

bascially, i can't see any reason why this wouldn't work... if you just do a
=COUNTIF(Input!$K$2:$K$1000,$J$18)
do you get a value >=1 and is that on the same line where all the other
criteria are true?

 

[Alan]

SUMPRODUCT works OK with the results of formulas, is it returning an
incorrect result or an error? I notice you have brackets missing in the
formula posted, is that a typo?
=SUMPRODUCT(--(Input!$G$2:$G$1000=$J$19),--(Input!$K$2:$K$1000=$J$18),--(Input!$A$2:$A$1000=A21)*(Input!$E$2:$E$1000))
alas, unchecked I'm not sure if you can mix the(--( way of doing it with the
* way,
=SUMPRODUCT(--(Input!$G$2:$G$1000=$J$19),--(Input!$K$2:$K$1000=$J$18),--(Input!$A$2:$A$1000=A21),--(Input!$E$2:$E$1000)
or
=SUMPRODUCT((Input!$G$2:$G$1000=$J$19)*(Input!$K$2:$K$1000=$J$18)*(Input!$A$2:$A$1000=A21)*(Input!$E$2:$E$1000))
Regards,