SUM if with 3 criteria
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? -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "ALex" wrote in message ... 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? |
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, "ALex" wrote in message ... 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? |
All times are GMT +1. The time now is 02:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com