Crazy Counting Question

guppie

New Member
Joined
Mar 13, 2006
Messages
14
Office Version
  1. 365
Platform
  1. Windows
I have an Excel sheet that shows the race for a list of students. I need to count how many of each race I have. However, there are some weird rules that come along with it.

If a student has a "Y" in only one of the race columns, they are considered that race and need to be counted as that race.
If a student has a "Y" in more than one column, than they are considered multi-racial.....unless they are White and Hispanic only, in which case they are considered Hispanic. But, if they have marked Hispanic and any other race than White, then they are considered multi-racial. If they select 3 or more races, they are considered multi-racial regardless of the selection.

A B C D E F G
Student Am. Ind. Asian Black Haw./Pac Is. White Hispanic
1 N N Y N Y N
2 N N Y N N N
3 N N N N Y Y
4 N N N N Y N
5 N Y N N Y Y
6 N Y N N N Y

In the example above:
Student 1 is Multi-Racial (Black and White)
Student 2 is Black
Student 3 is Hispanic (Both White and Hispanic only)
Student 4 is White
Student 5 is Multi-Racial because they have checked more than 2 races.
Student 6 is Multi-Racial because they checked only 2 races and they were Hispanic and something other than White.


I know that is really confusing, but I need to take a spreadsheet with this data on about 1000 kids and count how many I have of each. I thought about just using filters, but am looking to see if there is any way to create formulas that can be plugged into another cell that would count each race as described. My guess is no but would love to be wrong. I am a halfway competent user, but totally self taught.

Thanks in advance.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
My suggestion:

you could do this all in one formula but it would be a pain.

I'd set up a bunch of other columns to do the segmentation then use those to drive final results / calcs

With that simplification cna you make progress..?
 
Upvote 0
With the data layout as shown in Post # 1, try the following formulas:

Hispanic =SUMPRODUCT(--(MMULT(--($B$2:$G$2000="Y"),2^{5;4;3;2;1;0})={1,3}))
White =SUMPRODUCT(--(MMULT(--($B$2:$G$2000="Y"),2^{5;4;3;2;1;0})=2))
Haw./Pac Is. =SUMPRODUCT(--(MMULT(--($B$2:$G$2000="Y"),2^{5;4;3;2;1;0})=4))
Black =SUMPRODUCT(--(MMULT(--($B$2:$G$2000="Y"),2^{5;4;3;2;1;0})=8))
Asian =SUMPRODUCT(--(MMULT(--($B$2:$G$2000="Y"),2^{5;4;3;2;1;0})=16))
Am. Ind. =SUMPRODUCT(--(MMULT(--($B$2:$G$2000="Y"),2^{5;4;3;2;1;0})=32))
Multi =SUMPRODUCT(--(MMULT(--($B$2:$G$2000="Y"),2^{5;4;3;2;1;0})>3))-SUMPRODUCT(--(MMULT(--($B$2:$G$2000="Y"),2^{5;4;3;2;1;0})=2^{5,4,3,2}))
 
Upvote 0
Put this formula in (I am guessing) cell H2 (to identify the students race) and copy down...

=IF(COUNTIF(B2:G2,"Y")>2,"Multi-Racial",IF(F2&G2="YY","Hispanic",IF(COUNTIF(B2:G2,"Y")=2,"Multi-Racial",INDEX(A$1:G$1,MATCH("Y",A2:G2,0)))))

You can then use the COUNTIF function on Column H to get the totals for each race category.
 
Last edited:
Upvote 0
Solution
Another slight variation on what Rick has suggested. I'm using columns I:O to identify each student under their racial group, then totalling at the bottom.
Like Rick's column H this allows some checking of the categorisation should you wish to do that. It also keeps all the formulas fairly simple.
In this section I2:O7 I have used the custom cell format of "0;-0;;@" so that 0s don't show, making the table a bit easier to read/interpret (for me at least).

This section could be hidden or placed on another sheet if you want and the totals accessed by formulas (referencing the cells in row 8) elsewhere if needed.

Formula in I2 is copied across and down to row 7.
Formulas in N2:O2 are copied down to row 7.
Formula in I8 is copied across.

Excel Workbook
ABCDEFGHIJKLMNO
1StudentAm. Ind.AsianBlackHaw./Pac Is.WhiteHispanicAm. Ind.AsianBlackHaw./Pac Is.WhiteHispanicMulti
21NNYNYN  1
32NNYNNN1
43NNNNYY1
54NNNNYN1
65NYNNYY1
76NYNNNY1
80010113
Count Race Groups
 
Last edited:
Upvote 0
Thanks everyone. I ended up using Rick's idea because it was the cleanest for my purpose. It worked perfectly.
 
Upvote 0
Thanks everyone. I ended up using Rick's idea because it was the cleanest for my purpose. It worked perfectly.
You're welcome. Glad you got a successful outcome.

If you are interested, here are a couple of alternative formulas to achieve those results. Column H if you have the CONCAT function in your Excel version, otherwise column I.

Excel Workbook
BCDEFGHI
1Am. Ind.AsianBlackHaw./Pac Is.WhiteHispanicGroupGroup
2NNYNYNMulti-RacialMulti-Racial
3NNYNNNBlackBlack
4NNNNYYHispanicMulti-Racial
5NNNNYNWhiteWhite
6NYNNYYMulti-RacialMulti-Racial
7NYNNNYMulti-RacialMulti-Racial
Count Race Groups
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top