SUM if with 3 criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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?
 
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,
 
Back
Top