I'm trying to create a formula to count the numbers of colors chosen for a specific child, based on answered selected in various columns. For example, Marsha Chose Primary & Secondary Colors in her rainbow. Of the colors available to her, the count formula should return 5(She has 5 of the 6 colors she chose).I created a nested If formula, that doesn't work because it stops at the first Occurrence of the word Yes, and only returns a count of 3. I need to check all columns for a Yes and count the corresponding colors for any column that says yes, for whatever child I am counting.
The colors Available to Marsha, is typically housed in a separate tab, and changes for each child. However, I included it all in the same tab for ease of viewing.
Please keep in mind this is one small piece of a large formula, I'm hoping for a clean solution that doesn't add too much extra.
=IF(C6="Selected Colors",IF(D6="Yes",COUNTIF(A2:A6,"Red")+COUNTIF(A2:A6,"Yellow")+COUNTIF(A2:A6,"Blue"),IF(E6="Yes",COUNTIF(A2:A6,"Orange")+COUNTIF(A2:A6,"Purple")+COUNTIF(A2:A6,"Green"),IF(F6="Yes",COUNTIF(A2:A6,"Black")+COUNTIF(A2:A6,"White")))))
| Colors Available to Marsha | Child's Name | Options | Primary (Red,Yellow,Blue) | Secondary (Orange,Purple,Green) | Tertiary (Black,White) |
|---|---|---|---|---|---|
| Red | Timmy | All Colors | |||
| Blue | Susie | Selected Colors | Yes | Yes | |
| Orange | Ray | Selected Colors | Yes | ||
| Purple | Tod | Selected Colors | Yes | ||
| Yellow | Marsha | Selected Colors | Yes | Yes |
Your formula can be made to work if you slightly unnest it:
=IF(C6="Selected Colors",IF(D6="Yes",COUNTIF(A2:A6,"Red")+COUNTIF(A2:A6,"Yellow")+COUNTIF(A2:A6,"Blue"),0)+IF(E6="Yes",COUNTIF(A2:A6,"Orange")+COUNTIF(A2:A6,"Purple")+COUNTIF(A2:A6,"Green"),0)+IF(F6="Yes",COUNTIF(A2:A6,"Black")+COUNTIF(A2:A6,"White")))
To try and explain the logic a little: Your original formula was checking if there is a "Yes" in D6 and if that was true then adding together the counts of red, yellow and blue. If that is false (i.e. if there is no "Yes" in D6) then proceed to check if there is a "Yes" in E6, and if that is true add together the counts of orange, purple or green... and so on. Your formula will only check E6 if there is no "Yes" in D6.
Instead, you can set up the if statements to add to each other if(D6="yes" then R+Y+B) + if(E6="Yes" then O+P+G) + if(F6="Yes" then B+W). i.e. this gives 3 + 2 + 0
Your formula is impressively long, but if you just want a total of the colors that was used by Marsha would using something simpler like:
=IF(C6="Selected Colors",COUNTA(A2:A6),8)
(assuming in your case that All Colors = 8)