still learning to count
Posted by ed on March 28, 2001 6:15 AM
ok, i've tried everything suggested (See msg "counting with multiple criteria" below) but none of the suggestions are working. maybe i'm not explaining this as well as i should ..
in column J i have, among others, the name Tim. in column K i have either blank cells or the word Terminated. how can i count how many times they happen side by side? i'm beginning to think that you can't have more than one criteria for the COUNT or COUNTIF function. please tell me i'm wrong.
then, i have to count how many times Tim occurs in J with a blank cell beside it in K.
and THEN, i have to sum the numeric values in G when each of the 2 scenarios above occur. but i think i can figure this one out once the first/counting problem is solved.
sorry if this is getting old. i plan to give up after today if i can't get it to work. and thanks in advance for any time/energy spent on my stupid dilemma. :)
Posted by Mark W. on March 28, 2001 6:37 AM
Ed, I spent a considerable amount of time yesterday responding to your request. Those solutions can be found at:
13071.html
13071.html
13071.html
Don't be distracted by the use of SUM() when counting. Summing is the same as counting when the values that are being summed are 0s and 1s:
0
1
1
0
1
--
3
Also, as I indicated these formulas are array formulas which must be entered using Control+Shift+Enter.
Posted by Mark W. on March 28, 2001 6:45 AM
Revised URL list...
Posted by Aladin Akyurek on March 28, 2001 6:46 AM
Mark: You need to modify the first one.
=SUM(J2:J1790="Tim"*K2:K1790="terminated")
This needs parens around terms.
Aladin
Posted by Mark W. on March 28, 2001 6:51 AM
Ed, heed this advice...
Aladin, you're right! The formula should be:
=SUM((J2:J1790="Tim")*(K2:K1790="terminated"))
Posted by ed on March 28, 2001 7:00 AM
Re: i tried it
Thanks Mark, Aladin, etc., for your time yesterday and today, but I just tried exactly what you said and it's still not working. and yes i entered it as an array .. the formula seems to "work" (in that i don't get an error when entering it) but it lists a total of "0", when i can manually count at least 30 examples of Tim and Terminated being side by side. i'll look at the URLs that you listed and see if they help. thanks and sorry - trust that i'm 10x more frustrated about this than you are. have fun :)
Posted by Mark W. on March 28, 2001 7:02 AM
Ed, also...
...the formula at ...
...should have parentheses as shown below:
{=SUM((J2:J1790="Tim")*(K2:K1790="terminated")*G2:G1790)}
Posted by ed on March 28, 2001 8:08 AM
my apologies, and a follow up ?
ok, mark, aladin, etc. .. thank you so much. for the sake of argument/my sanity i started on a clean spreadsheet and tried the formula you gave me. .. it works. there must be something corrupt about the existing spreadsheet that i'm trying to work with. .. just out of curiousity, does anyone know what could cause this? and is there a way to correct this mysterious problem without rekeying 2000 lines of data?
Posted by Mark W. on March 28, 2001 8:18 AM
No apologies needed...
I feel bad about not including the necessary parentheses...thanks go out to Aladin.
Is it possible that the any empty cells in column G actually contained 1 or more spaces? If so, an attempt to multiply (using the multiplication operator, *) would produce a #VALUE! error.
