How to count number of occurences of two different things at once?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone know how I would count the number of times two things happen? For
instance there are the following columns

Column A: FirstName
Column B: LastName
Column C: Quadrant
Column D: Group

There are four different quadrants (i.e. 1,2,3,4) and three different groups
(Adult, youth, child).
How do I calculate how many Adults are in quadrant 1, how many in quadrant
2, how many in quadrant 3, etc. and the same for the youth and child groups?
I have tried many many times...
Please help,
Cairan
 
=SUMPRODUCT(--($C$":$C$20=1),--($D$2:$D$20="Adult"))

etc


If you put the quadrant and group in cells, it will be easier to drag the
fromula down, auto updating, such as


=SUMPRODUCT(--($C$":$C$20=M1),--($D$2:$D$20=N1))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Dear Bob,
Thank you for your reply. I am afriad that it still does not work. Let me
explain better. There are 100s of entries and people's names as well as other
data is there but I need to know how many people are in each quadrant and
which group (i.e. adult, youth, child) there are.

I read up on sumproduct and I do not want to sum their values I want to
count how many.

PLEASE HELP!
Cairan
 
Try it one time.

You may be surprised.
Dear Bob,
Thank you for your reply. I am afriad that it still does not work. Let me
explain better. There are 100s of entries and people's names as well as other
data is there but I need to know how many people are in each quadrant and
which group (i.e. adult, youth, child) there are.

I read up on sumproduct and I do not want to sum their values I want to
count how many.

PLEASE HELP!
Cairan
Click to expand...
 
Dear Bob,
After trying it numerous times you are correct but there was one small error
in the code. It should read:
=SUMPRODUCT(--($C$2:$C$20=1),--($D$2:$D$20="Adult"))

Thanks!!!
Yours,
Cairan
 
Dear Dave,
I had tried it numerous times and it kept having an error but I was changing
the wrong part of it.
The code should read:

=SUMPRODUCT(--($C$2:$C$20=1),--($D$2:$D$20="Adult"))

THANKS!
Cairan
 
Typo because I had to shift the $ sign and didn't release it quick enough
when doing the 2 :-(

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top