Sum/Count Formula Help

Posted by Kim B on October 23, 2000 11:33 AM

I need a formula that will evaluate data in two different columns then count the occurences when both criteria are met. Column S contains numbers and column T contains text (names). I tried the array formula =SUM(S:S>0)*(T:T="name"), but get the error message #num!. If I don't use Control-Shift-Enter, I get either 0 or 1 as the result, not the total.

Thanks in advance for any help.

Posted by Tim Francis-Wright on October 23, 2000 11:47 AM

I got a real answer by using your formula,
but substituting, say, S1:s10000 and T1:T10000
for S:S and T:T.

HTH

Posted by Tim Francis-Wright on October 23, 2000 11:48 AM

I got a real answer by using your formula,
but substituting, say, S1:s10000 and T1:T10000
for S:S and T:T, and using an extra set of parens:
=sum((S1:S10000>0)*(T1:T10000="name"))

HTH

Posted by Kim B on October 23, 2000 1:14 PM

I tried this and I do get a number, but it's also counting blank cells. Any suggestions?

Posted by Ivan Moala on October 24, 2000 12:58 AM

1st - Array formulas will not work over Coloumn
data ranges eg S:S etc
2nd - You must have spaces in some data ranges
eg " ".......suggest you add an extra condition
for this eg.

=SUM(ISNUMBER(S1:S10000)*(S1:S10000>0)*(T1:T10000="name"))

assuming your data range is in S

HTH

Ivan

Posted by KimB on October 24, 2000 5:51 AM

It works!!! Thank You.