marstonk said:
=COUNTA((SUMPRODUCT((Development!$H$4:$H$42='SO Rents'!A5),(Development!$K$4:$K$42='SO Rents'!$F$5))),Development!$P$4:$P$42)
Hi this is what I need to do count how many times a value appears in column P when the criterea is met. Does this make sense? It is returning the same value for each row so it doesnt work any recommendations on how to get it to count the times a value occurs when the condition is met?
Thanks
struggling along kitty g
ray:
Kitty,
=SUMPRODUCT(--(Development!$H$4:$H$42='SO Rents'!A5),--(Development!$K$4:$K$42='SO Rents'!$F$5),Development!$P$4:$P$42)
gives you a multiconditional sum, that is, every value from $P$4:$P$42 is added up when $H$4:$H$42 is A5 and $K$4:$K$42 is F5.
=SUMPRODUCT(--(Development!$H$4:$H$42='SO Rents'!A5),--(Development!$K$4:$K$42='SO Rents'!$F$5))
gives you a multiconditional count, that is, 1 when when $H$4:$H$42 is A5 and $K$4:$K$42 is F5. And all ones are summed up. Summing a bunch of individual 1's is identical to counting those 1's.
BTW, you must not omit the -- bit, which converts the truth values (TRUE/FALSE) into numbers (1/0).