Counting occurrences when the words can fit multiple meanings

[claytont454]

Hi folks, I've been solving this so far by doing lots of steps and thought someone might have a better idea.
My real application is counting vaccinations that are given in different combinations, but to make this shorter, I'll pretend that the people are students in a painting class that ran for 3 sessions. Each student can use red, blue, or yellow each day, and if they use green or orange, those count as 1 each of the primary colors (green=1 blue + 1 yellow, orange=1 red + 1 yellow). Here is their color usage, followed by the results that I'm calculating manually.

[TABLE="class: grid, width: 521"]
<colgroup><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Color used[/TD]
[TD]Session1[/TD]
[TD]Session2[/TD]
[TD]Session3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Billy[/TD]
[TD]Red[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Billy[/TD]
[TD]Orange[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Billy[/TD]
[TD]Blue[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sally[/TD]
[TD]Purple[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Sally[/TD]
[TD]Blue[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Gina[/TD]
[TD]Green[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Fred[/TD]
[TD]Yellow[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Fred[/TD]
[TD]Orange[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

How can I use Excel to get the following table, hopefully with little or no programming?
Thanks in advance for your ideas!
Results: How many of each color did each student use?
[TABLE="class: grid, width: 320"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Red[/TD]
[TD]Yellow[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Billy[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sally[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Gina[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Fred[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[yky]

I saw Billy use one red, one orange, three blue, and no yellow. Why are there two red and one yellow in the result table?

 

[Pimagine]

For my understanding: didn't Gina use 2 units of yellow and 2 units of blue instead of 1 each? And didn't Fred use 3 units of yellow instead of 2?

 

[Pimagine]

First I added three columns next to the main table for 'red', 'yellow' and 'blue'. Then I added a small table below that, telling me which colors green, orange and purple are made of. Finally I placed the table with the totals in the left lower corner:

[TABLE="width: 522"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Color used[/TD]
[TD]Session1[/TD]
[TD]Session2[/TD]
[TD]Session3 [/TD]
[TD]red[/TD]
[TD]yellow[/TD]
[TD]blue[/TD]
[/TR]
[TR]
[TD]Billy[/TD]
[TD]red[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Billy[/TD]
[TD]Orange[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Billy[/TD]
[TD]Blue[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD]Purple[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD]Blue[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Gina[/TD]
[TD]Green[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Yellow[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Orange[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Red[/TD]
[TD]Yellow[/TD]
[TD]Blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Billy[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]green[/TD]
[TD]blue[/TD]
[TD]yellow[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]orange[/TD]
[TD]red[/TD]
[TD]yellow[/TD]
[/TR]
[TR]
[TD]Gina[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]purple[/TD]
[TD]red[/TD]
[TD]blue[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Then I added the following formula in cell F2 which is the first cell in the 'red' column:

=IF(OR($B2=F$1,NOT(ISNA(HLOOKUP(F$1,INDEX($G$13:$H$15,MATCH($B2,$F$13:$F$15,0),0),1,0)))),SUM($C2:$E2),0)

Then I copied this formula to all other cells below 'red ....blue'.

Finally, I added the following formula in the cell of the totals table at the intersection of 'Billy' and 'red':

=SUMIF($A$2:$A$9,$A13,$F$2:$F$9)

and copied this one in all other cells for Sally, Gina and Fred.

I hope this will work for you.

Btw: how can I add neat images of spreadfsheets to posts?

 

[azumi]

Maybe something like this? Please follow the link to download the file

https://www.dropbox.com/s/2149s62xe2ep3kt/Summing.xlsx

Azumi

 

[claytont454]

Yes, you are correct. I have no idea how I miscounted those in my results table.

 

[claytont454]

To add borders to a table, you click your mouse inside one of the cells then look for an icon at the top of your editing window.
One of the icons is for "Table properties", and you can change the Borders to show the whole grid or just the border.