COUNT CELLS WITH MORE THAT ONE CRITERIA

Posted by RAY VEGA on July 13, 2001 2:15 PM

i NEED TO KNOW HOW TO COUNT A GROUP OF CELLS OF ROWS BY SELECTING MULTIPLE CRITERIAS, FOR EXAMPLE:
I WANT TO COUNT THE ROWS WITH THE GENDER TO BE "MALE" AND WITHIN THOSE I WANT TO COUNT THE ROWS WITH THE THE STATE TO BE "NJ". i ONLY WANT TO COUNT MALES FROM NJ.

THANKS,

RAY VEGA

Posted by Aladin Akyurek on July 13, 2001 2:41 PM

lets say that you have gender values in A2:A20 and states in B2:B20.
Use the following array formula to get the desired count:

=SUM((A1:A20="Male")*(B2:B20="NJ"))

You need to hit CONTROL+SHIFT+ENTER at the same time (not just ENTER) to enter an array formula.

Aladin

Posted by Ben O. on July 13, 2001 2:42 PM

This formula will work.

=SUM((B2:B11="M")*(C2:C11="NJ"))

Change B2:B:11 to the range that contains the genders. Change C2:C11 to the range that has the state.

When you enter the formula, hold Ctrl and Shift and press enter, since it is an array formula.

-Ben

Posted by David on July 16, 2001 8:19 AM

Why don't you use the AutoFilter command from the Data Menu. Then Subtotal.

Posted by David Megnin on July 16, 2001 2:38 PM

I have a very similar problem, except in my worksheet in A1:A20 the values could be "Male", "M" or "Man". I tried replacing
=SUM((A1:A20="Male")*(B2:B20="NJ")) with
=SUM((A1:A20="*M*")*(B2:B20="NJ")) but that did not work. Any ideas please?

Thanks in advance!
David Megnin
megnin@nortelnetworks.com

: i NEED TO KNOW HOW TO COUNT A GROUP OF CELLS OF ROWS BY SELECTING MULTIPLE CRITERIAS, FOR EXAMPLE

Posted by Aladin Akyurek on July 16, 2001 3:17 PM

=SUM((A1:A20="Male")*(B2:B20="NJ")) with =SUM((A1:A20="*M*")*(B2:B20="NJ")) but that did not work. Any ideas please? David Megnin megnin@nortelnetworks.com

DAVID -- TRY

=SUM((ISNUMBER(SEARCH(A1:A20,"M*")))*(B2:B20="NJ"))

INSTEAD. STILL AS AN ARRAY FORMULA.

Aladin

================ : Use the following array formula to get the desired count:

Posted by David Megnin on July 17, 2001 2:50 PM

Getting closer...

I was hoping to just put a similar expression on the right side so solve the same problem...

In my B cloumn "NJ" rarely (never) just "NJ" it's "somewhere near NJ" or "123NJ321" or "any nj 001", so I need the same wildcard setup on the right side.

When I tried this:
=SUM((ISNUMBER(SEARCH(B1:B666,"leslie"))+0)*(SUM((ISNUMBER(SEARCH(D1:D666,"otm"))+0))))
It multiplies the results of "leslie" and "otm" together instead of giving me how many "otm" matches "leslie".

Thanks again,
David : =SUM((A1:A20="Male")*(B2:B20="NJ")) with : =SUM((A1:A20="*M*")*(B2:B20="NJ")) but that did not work. Any ideas please? : David Megnin : megnin@nortelnetworks.com

Posted by Aladin Akyurek on July 17, 2001 3:26 PM

David,

My mistake. SEARCH's 1st arg must be substring, 2nd arg the string to search in. So, the array formula must run as:

=SUM((ISNUMBER(SEARCH("Leslie",A1:A4)))*(ISNUMBER(SEARCH("otm",B1:B4))))

I think you don't even need using * in specifying the substring. Given the following data, I get a count of 3:

{"leslie","XotmN";"Leslie","otm";"john","otmB";"Leslie","votm xj"}

Aladin

======== =SUM((ISNUMBER(SEARCH(B1:B666,"leslie"))+0)*(SUM((ISNUMBER(SEARCH(D1:D666,"otm"))+0)))) It multiplies the results of "leslie" and "otm" together instead of giving me how many "otm" matches "leslie". David : I have a very similar problem, except in my worksheet in A1:A20 the values could be "Male", "M" or "Man". I tried replacing

Posted by David Megnin on July 19, 2001 1:45 PM

Thank you!! It's perfect.
I can now put this formula in one cell of my table:
=SUM((ISNUMBER(SEARCH($H3,$B$1:$B$2000))+0)*(ISNUMBER(SEARCH(I$2,$D$1:$D$2000))+0))
...and copy it down and accross as needed and change any column or row header as needed.

Thanks again! : =SUM((ISNUMBER(SEARCH(B1:B666,"leslie"))+0)*(SUM((ISNUMBER(SEARCH(D1:D666,"otm"))+0)))) : It multiplies the results of "leslie" and "otm" together instead of giving me how many "otm" matches "leslie". Thanks again, : David