Crazy Counting Question

[guppie]

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.

 

[PaddyD]

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..?

 

[Tetra201]

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}))

 

[Rick Rothstein]

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.

 

[Peter_SSs]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Student	Am. Ind.	Asian	Black	Haw./Pac Is.	White	Hispanic		Am. Ind.	Asian	Black	Haw./Pac Is.	White	Hispanic	Multi
2	1	N	N	Y	N	Y	N								1
3	2	N	N	Y	N	N	N				1
4	3	N	N	N	N	Y	Y							1
5	4	N	N	N	N	Y	N						1
6	5	N	Y	N	N	Y	Y								1
7	6	N	Y	N	N	N	Y								1
8									0	0	1	0	1	1	3
Count Race Groups

 

[guppie]

Thanks everyone. I ended up using Rick's idea because it was the cleanest for my purpose. It worked perfectly.

 

[Peter_SSs]

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
	B	C	D	E	F	G	H	I
1	Am. Ind.	Asian	Black	Haw./Pac Is.	White	Hispanic	Group	Group
2	N	N	Y	N	Y	N	Multi-Racial	Multi-Racial
3	N	N	Y	N	N	N	Black	Black
4	N	N	N	N	Y	Y	Hispanic	Multi-Racial
5	N	N	N	N	Y	N	White	White
6	N	Y	N	N	Y	Y	Multi-Racial	Multi-Racial
7	N	Y	N	N	N	Y	Multi-Racial	Multi-Racial
Count Race Groups