Counting with multiple criteria
Posted by ed on March 27, 2001 11:31 AM
hi there. .. i'm a temp and i just made the mistake of answering "yes" when asked if i "know excel formulas?" ..
i need to first count the # of times "Tim" occurs on column J AND "terminated" occurs in K, at the same time. i've tried all of the variations i can think of but get a total of 0, when i'm sure that there are several occurances.
these are what i've tried:
=COUNT(J2:J1790="Tim",K2:K1790="terminated")
=COUNTIF(J2:J1790="Tim",K2:K1790="terminated")
=COUNT(IF(J2:J1790="Tim",K2:K1790="terminated"))
i've tried the CTRL SHIFT ENTER option too - to no avail.
once i get this done, i then have to SUM column G for every time Tim and terminated occur together ..
and then the SUM of every time they don't occur together.
is this making any sense? i hope so.
thanks,
ed
Posted by Mark W. on March 27, 2001 11:34 AM
{=SUM(J2:J1790="Tim"*K2:K1790="terminated")}
Posted by Loren on March 27, 2001 11:36 AM
Countif won't let us use two conditions. How about creating a
new column combining J and K text, then counting that column?
Posted by Mark W. on March 27, 2001 11:39 AM
Here's you SUM of column G...
{=SUM(J2:J1790="Tim"*K2:K1790="terminated"*G2:G1790)}
Precisely, what do you mean by "...every time they
don't occur together".
Posted by Mark W. on March 27, 2001 11:52 AM
Here's your SUM of column G...when they don't occur together.
Oh, now I get it! Try this:
{=SUM(IF(NOT(J2:J1790="Tim")+NOT(BK2:K1790="terminated"),1)*G2:G1790)}
Note: this solution is based on DeMorgan's Law
Posted by Mark W. on March 27, 2001 11:53 AM
Oops, Typo! Re: Here's your SUM of column G...when they don't occur together.
Posted by Mark W. on March 27, 2001 12:05 PM
DeMorgan's Law
http://java.cms.livjm.ac.uk/treefrog/hndcomp/demorgan.htm
Posted by ed on March 27, 2001 12:10 PM
thanks for the quick reply .. but now i get the #VALUE! error. i only want it to count the occurances of Tim in J with Terminated in K. .. i tried replacing SUM with COUNT in your formula, but i get 0, which makes no sense considering i can manually count at least 30 times when Tim is next to Terminated. AIGH!!!
Posted by Mark W. on March 27, 2001 12:14 PM
Loren, by the way...
You know that problem we were having with
=INDIRECT(A3&" ",C1)... I submitted a "bug"
report to Microsoft. They may never send
back a response, so I may never know if it
is. But, I'll check their Knowledge Base
from time-to-time to see if they post
something that acknowledges this problem.
Posted by Mark W. on March 27, 2001 12:19 PM
Ed, sorry, since you mentioned Control+Shift+Enter
I though you'd understand that these are array
formulas that require the Control+Shift+Enter
key combination. I might as well mention that
the braces, {}, are not typed by you... they're
supplied by Excel to signify that the formula
has been entered as an array formula.
Posted by Mark W. on March 27, 2001 12:44 PM
My typing goes to hell when I'm multi-tasking...
Posted by Loren on March 27, 2001 1:04 PM
Loren, by the way...OK, noted
