# Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Hello, I am trying to create a total color column? Can anyone help?
Solved by I. A. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 26/04/2018 - 10:24
Hi
Excelchat Expert 26/04/2018 - 10:24
Hello! Welcome to Excel chat.
User 26/04/2018 - 10:24
I believe I'm using 2010
User 26/04/2018 - 10:24
Thanks! I hope you can help!!!
Excelchat Expert 26/04/2018 - 10:24
I understand that you want to make a total column. Please share more details about that.
User 26/04/2018 - 10:25
sure
Excelchat Expert 26/04/2018 - 10:25
It would be good, if we can have some sample data as well. Only 2-3 rows of data should be sufficient.
User 26/04/2018 - 10:25
I'm creating an evaluation worksheet that's rated on color. Green, Yellow, Red
Excelchat Expert 26/04/2018 - 10:25
Ok.
User 26/04/2018 - 10:26
I have 3 yes or no questions - yes would = green and no would = red. I also have 3 green, yellow, red questions.
Excelchat Expert 26/04/2018 - 10:26
Ok.
User 26/04/2018 - 10:27
I need color total to capture the accurate rating. I hope that clears up any confusion.. Need more info?
Excelchat Expert 26/04/2018 - 10:27
Ok.
Excelchat Expert 26/04/2018 - 10:28
Yes, sure I need to understand better.
Excelchat Expert 26/04/2018 - 10:28
Let me ask my questions based on the understanding so far.
User 26/04/2018 - 10:28
kool
Excelchat Expert 26/04/2018 - 10:28
Do you want the total for each color?
User 26/04/2018 - 10:29
in that regard. Yes/Green can = 3, yellow would = 2 and No/red would = 1
Excelchat Expert 26/04/2018 - 10:29
Ok.
Excelchat Expert 26/04/2018 - 10:30
Ok. I have added some dummy data in the online sheet.
User 26/04/2018 - 10:30
for example if column c reflected all greens I need the total to be the color green
Excelchat Expert 26/04/2018 - 10:30
Ok.
User 26/04/2018 - 10:30
I can't see it
Excelchat Expert 26/04/2018 - 10:30
User 26/04/2018 - 10:31
will is loose you if do?
Excelchat Expert 26/04/2018 - 10:31
No. You won't.
User 26/04/2018 - 10:32
I like it.. Now we need a total column that would choose the best or highest color (make sense?)
Excelchat Expert 26/04/2018 - 10:32
Ok.
Excelchat Expert 26/04/2018 - 10:32
Let's first capture total of each color, then we can devise a way to choose the best or highest color.
User 26/04/2018 - 10:33
ooh.. sounds promising
User 26/04/2018 - 10:33
how do we get started?
Excelchat Expert 26/04/2018 - 10:33
Just hold on. I am adding formula.
User 26/04/2018 - 10:34
okay, my apologies
Excelchat Expert 26/04/2018 - 10:35
No worries. Now there are totals in column D, based on the weight given to each color (Green =3, Yellow = 2, Red = 1)
Excelchat Expert 26/04/2018 - 10:35
Now basis that, we can see that max is for green.
User 26/04/2018 - 10:35
I'm lost, can you explain it differently.. sorry:(
Excelchat Expert 26/04/2018 - 10:35
Ok
Excelchat Expert 26/04/2018 - 10:36
Please see the totals in column D.
User 26/04/2018 - 10:36
I may have it.. the total for D2 should be 2 not 3 as red = 1
Excelchat Expert 26/04/2018 - 10:36
But there are three Red in data.
User 26/04/2018 - 10:37
okay, hold on
User 26/04/2018 - 10:37
I see the two green and the one additional green in B7.
User 26/04/2018 - 10:37
that totals 9 - yes?
Excelchat Expert 26/04/2018 - 10:37
Don't look at column C yet.
User 26/04/2018 - 10:37
ah, got it now
Excelchat Expert 26/04/2018 - 10:37
That's just labels for our color totals.
User 26/04/2018 - 10:38
whew!!!! clarity is wonderful
Excelchat Expert 26/04/2018 - 10:38
My apologies for mixing them up.
Excelchat Expert 26/04/2018 - 10:38
Now we can focus on column E as the result column.
Excelchat Expert 26/04/2018 - 10:38
B is the column where our data is.
User 26/04/2018 - 10:38
no worries..
Excelchat Expert 26/04/2018 - 10:39
In column B, there are total 7 entries, 2 Green, 3 Red, 2 Yellow.
Excelchat Expert 26/04/2018 - 10:39
Right?
User 26/04/2018 - 10:39
yep
Excelchat Expert 26/04/2018 - 10:40
Ok great! I have simply counted the number of each color in column B and multiplied it by their weight.
User 26/04/2018 - 10:40
Excelchat Expert 26/04/2018 - 10:40
And that gives me the total for that color respectively.
User 26/04/2018 - 10:40
yep got it!
Excelchat Expert 26/04/2018 - 10:41
Now, that we have the total value for each color, we can use it to determine the best color.
Excelchat Expert 26/04/2018 - 10:41
If we take simple criteria of the highest value, then the best color would be 'Green'.
Excelchat Expert 26/04/2018 - 10:41
User 26/04/2018 - 10:41
sessions going to end soon
User 26/04/2018 - 10:42
i know that but how to I reflect that in a formula?
Excelchat Expert 26/04/2018 - 10:42
No worries. You can still extend it for one more session. You already on the 2nd one now.
User 26/04/2018 - 10:42
okay
User 26/04/2018 - 10:42
thx
Excelchat Expert 26/04/2018 - 10:42
Ok. If you need a single line formula, then we can use a Max function.
Excelchat Expert 26/04/2018 - 10:42
Let me show you that.
User 26/04/2018 - 10:43
hmmm. explain
Excelchat Expert 26/04/2018 - 10:44
We will use a nested if to get the color name.
User 26/04/2018 - 10:44
thx
User 26/04/2018 - 10:44
nested?
User 26/04/2018 - 10:44
I'm unfamiliar with that term
Excelchat Expert 26/04/2018 - 10:44
An IF function within other IF function.
User 26/04/2018 - 10:45
sounds complex but I guess it would have to be
User 26/04/2018 - 10:46
you still with me
Excelchat Expert 26/04/2018 - 10:47
Yeah I am working on the formula.
User 26/04/2018 - 10:47
kool
Excelchat Expert 26/04/2018 - 10:48
There is a partial formula in E5 right now.
Excelchat Expert 26/04/2018 - 10:48
I am modifying it further.
User 26/04/2018 - 10:49
looks good. For the example you provided. As red and yellow equal red would trump the yellow.
User 26/04/2018 - 10:49
Wow!!! FABU
Excelchat Expert 26/04/2018 - 10:50
I hope that solves your problem.
Excelchat Expert 26/04/2018 - 10:50
:)
User 26/04/2018 - 10:50
me too.. I think it will.. Can you share the formula?
Excelchat Expert 26/04/2018 - 10:50
=if(countIF(B1:B7,"Green")*3 >countIF(B1:B7,"Yellow")*2, If(countIF(B1:B7,"Green")*3 > countIF(B1:B7,"Red")*1, "Green", "Red"),if(countIF(B1:B7,"Yellow")*2 >countIF(B1:B7,"Red")*1,"Yellow", "Red") )
Excelchat Expert 26/04/2018 - 10:51
User 26/04/2018 - 10:51
how?
Excelchat Expert 26/04/2018 - 10:51
Go to File menu on the sheet.
Excelchat Expert 26/04/2018 - 10:51
Excelchat Expert 26/04/2018 - 10:52
Select the format as Microsoft Excel and save it to the folder you wish to on your local drive.
User 26/04/2018 - 10:52
done
Excelchat Expert 26/04/2018 - 10:52
Cool
Excelchat Expert 26/04/2018 - 10:52
Should I explain the formula now?
User 26/04/2018 - 10:52
Oh my gosh.. Thank you so much!!!!
Excelchat Expert 26/04/2018 - 10:53
Should I explain this formula to you?
User 26/04/2018 - 10:54
I think so
Excelchat Expert 26/04/2018 - 10:54
Ok. Fine.
User 26/04/2018 - 10:54
because I'm still struggling to apply it
Excelchat Expert 26/04/2018 - 10:54
Ok. I will explain it so that you can modify to suit your needs.
User 26/04/2018 - 10:54
thank you!
Excelchat Expert 26/04/2018 - 10:55
There are two parts of the formula, first is to calculate the total for each color.
Excelchat Expert 26/04/2018 - 10:55
You can ignore D1 to E3 for now, and lets focus just on E5.
Excelchat Expert 26/04/2018 - 10:56
So, the first part is calculating total for a color and multiplying it by its weight.
Excelchat Expert 26/04/2018 - 10:56
This is being done by using a COUNTIF function.
Excelchat Expert 26/04/2018 - 10:56
COUNTIF is the function to count values using a criteria.
Excelchat Expert 26/04/2018 - 10:57
As you would see, I have used B1:B7 is the range where I need counting, and second input is the color name within double quotes.
Excelchat Expert 26/04/2018 - 10:57
To apply this formula to your case, you just need to change the range in each COUNTIF function.
Excelchat Expert 26/04/2018 - 10:58
So if your data is C2 to C100, you will need to put C2:C100 in each COUNTIF function in the formula.
Excelchat Expert 26/04/2018 - 10:59
Now, the remaining explanation: I have used multiple IF in a main IF.
Excelchat Expert 26/04/2018 - 10:59
In main IF, I am checking if total of Green is larger than total of Yellow.
User 26/04/2018 - 11:00
Question.. I've adjusted the formula but the yielding color is yellow instead of red
User 26/04/2018 - 11:00
IF(COUNTIF(E11:E17,"Green")*3 >COUNTIF(E11:E17,"Yellow")*2, IF(COUNTIF(E11:E17,"Green")*3 > COUNTIF(E11:E17,"Red")*1, "Green", "Red"),IF(COUNTIF(E11:E17,"Yellow")*2 >COUNTIF(E11:E17,"Red")*1,"Yellow", "Red") )
User 26/04/2018 - 11:00
Excelchat Expert 26/04/2018 - 11:01
Are Red and Yellow equal?
User 26/04/2018 - 11:01
they are no
User 26/04/2018 - 11:01
sorry they are not equal
Excelchat Expert 26/04/2018 - 11:02
Then formula is right.
User 26/04/2018 - 11:02
there's more red than yellow so the rating should be red
Excelchat Expert 26/04/2018 - 11:02
But the weight of Yellow is more, so that's why Yellow may come up.
User 26/04/2018 - 11:02
I also have to include a rating of yes or no answers
Excelchat Expert 26/04/2018 - 11:03
If number of Reds are more than double of number of Yellows, only then answer can be Red
User 26/04/2018 - 11:03
eliminating yes or no answers would yield a yellow rating
Excelchat Expert 26/04/2018 - 11:03
Else it will always be Yellow.
User 26/04/2018 - 11:03
is it possible to include a yes or no rating with the formula that you created?
Excelchat Expert 26/04/2018 - 11:04
It is, but that will make formula way too complex. And I would advise against that.
User 26/04/2018 - 11:04
any suggestions?
User 26/04/2018 - 11:05
I could create a second column to rate the yes or no answers
Excelchat Expert 26/04/2018 - 11:05
Then you can write a result table, as we did initially in D1 to E3.
User 26/04/2018 - 11:05
then divide the two columns? does that make sense
Excelchat Expert 26/04/2018 - 11:06
I didn't get that.
User 26/04/2018 - 11:06
yeah.. I was just trying to make sense :)
User 26/04/2018 - 11:06
I can have two total columns
User 26/04/2018 - 11:06
one for the green, yellow, red
User 26/04/2018 - 11:07
and the other for the yes or no
Excelchat Expert 26/04/2018 - 11:07
Ok. Yeah, that can be done.
User 26/04/2018 - 11:07
Kool.. can you show me?
Excelchat Expert 26/04/2018 - 11:08
Please see the cell E6 now.
User 26/04/2018 - 11:09
I see it
Excelchat Expert 26/04/2018 - 11:09
I have two columns now, and we can know whether "Yes" or "No" has the most numbers.
User 26/04/2018 - 11:10
not to add more oil to the fire but yes = green and no = red
User 26/04/2018 - 11:11
is it possible to intertwine the two to get an accurate rating?
Excelchat Expert 26/04/2018 - 11:11
Aah Ok.
User 26/04/2018 - 11:11
between the best color and the most yes or no
User 26/04/2018 - 11:11
awesome!!!
Excelchat Expert 26/04/2018 - 11:11
Just hold on.
User 26/04/2018 - 11:11
:)
Excelchat Expert 26/04/2018 - 11:13
It's difficult now.
User 26/04/2018 - 11:13
oh man..
User 26/04/2018 - 11:13
shoot
Excelchat Expert 26/04/2018 - 11:13
As I don't know the whole context, I am not able to find the relation between those two.
User 26/04/2018 - 11:14
okay.. I'll try to explain
Excelchat Expert 26/04/2018 - 11:14
I understand that you gave colors based on the Yes / No answers only.
Excelchat Expert 26/04/2018 - 11:14
So adding a second column of Yes / No will not add any value to it.
User 26/04/2018 - 11:15
okay so change the yes to equal 3 and the no to equal 1. 3 in turn = green and 1 would in turn = red
Excelchat Expert 26/04/2018 - 11:15
In my opinion, it will just doubles up Yes / No weight. And more so for Green.
Excelchat Expert 26/04/2018 - 11:15
I have already done that in E6. I have given Yes = 3, Red = 1.
Excelchat Expert 26/04/2018 - 11:16
But I don't know how to combine best color and most yes to get a final color. As I don't see any value in it.
User 26/04/2018 - 11:16
is there a way to create a value?
Excelchat Expert 26/04/2018 - 11:16
And in my opinion, will not reflect a right rating.
Excelchat Expert 26/04/2018 - 11:17
My suggestion is to use only either color or Yes / no to get a rating.
Excelchat Expert 26/04/2018 - 11:17
Use only 1 of them.
User 26/04/2018 - 11:17
okay..
User 26/04/2018 - 11:17
let's do it..
Excelchat Expert 26/04/2018 - 11:18
Then the best color formula is the right one.
Excelchat Expert 26/04/2018 - 11:18
You can ignore column C now.
User 26/04/2018 - 11:18
sounds good!!!
User 26/04/2018 - 11:18
for those three yes/no questions, 3 and 1 would be the numbers to choose from to equal green or red
Excelchat Expert 26/04/2018 - 11:18
Yes.
User 26/04/2018 - 11:18
sounds like a plan..
Excelchat Expert 26/04/2018 - 11:18
And that's the formula in E7.
User 26/04/2018 - 11:19
for column C?
Excelchat Expert 26/04/2018 - 11:19
yes.
User 26/04/2018 - 11:19
okay,.. will you show me the formula?
Excelchat Expert 26/04/2018 - 11:19
=If(countif(C1:C7, "Yes")*3 > CountIf(C1:C7, "No")*1, "Green", "Red")
Excelchat Expert 26/04/2018 - 11:20
You should save the file now locally.
User 26/04/2018 - 11:21
Wow!! That was amazing... Thanks so much for your help!!!
User 26/04/2018 - 11:21
Excelchat Expert 26/04/2018 - 11:21
Excelchat Expert 26/04/2018 - 11:22
Although we are not allowed to share our personal info, my first name is Pulkit.
User 26/04/2018 - 11:22
Well Pulkit.. You saved the day!!! :)
Excelchat Expert 26/04/2018 - 11:22
Thanks for kind words.
User 26/04/2018 - 11:22
I'm ever so grateful!!!
Excelchat Expert 26/04/2018 - 11:22
Hope to see you here again soon. Have a nice day ahead.
User 26/04/2018 - 11:23
You too!!!
Excelchat Expert 26/04/2018 - 11:23
Bye!
Excelchat Expert 26/04/2018 - 11:24
You can end this session now by clicking END button above. And please don't forget to rate your experience.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.