# Excel - COLUMN Function Problem - Expert Solution

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

how to make a bar chart that displays the number of variants that exist in a column?
Solved by S. L. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 18/08/2018 - 06:53
hello there
User 18/08/2018 - 06:53
hi
Excelchat Expert 18/08/2018 - 06:53
Do you have a file to work on?
User 18/08/2018 - 06:54
yes i have
Excelchat Expert 18/08/2018 - 06:54
User 18/08/2018 - 06:55
Excelchat Expert 18/08/2018 - 06:56
Yes, now can you please guide me on what is needed from this?
User 18/08/2018 - 06:57
I need to make a bar chart that shows how many variants are existing in the N column
User 18/08/2018 - 06:57
like, no of Local workshops and no of Auth workshops
User 18/08/2018 - 06:58
can we make it?
Excelchat Expert 18/08/2018 - 06:59
which column represents no of Local workshops and no of Auth workshops?
User 18/08/2018 - 06:59
the N column
User 18/08/2018 - 07:00
but it does not represent the no. of workshops
Excelchat Expert 18/08/2018 - 07:00
So number of times Local workshops is on column N is the number for that?
User 18/08/2018 - 07:00
yes yes yes
User 18/08/2018 - 07:00
exactly
User 18/08/2018 - 07:02
u thr?
Excelchat Expert 18/08/2018 - 07:02
Here check sheet 1
User 18/08/2018 - 07:03
can you tell me how to do it?
Excelchat Expert 18/08/2018 - 07:04
Sure wait, I am making it a bit more polished
User 18/08/2018 - 07:04
User 18/08/2018 - 07:04
the time is running out
Excelchat Expert 18/08/2018 - 07:04
Here
Excelchat Expert 18/08/2018 - 07:05
So lets start
Excelchat Expert 18/08/2018 - 07:05
First we need to count how many times Local and Authorised workshop are there in Column N
User 18/08/2018 - 07:05
oh., can't we skit it anyway?
User 18/08/2018 - 07:06
*skip
Excelchat Expert 18/08/2018 - 07:06
No, as we need a value for how many times those shops are there so we need a count
User 18/08/2018 - 07:07
oh., then how to calculate the count?
Excelchat Expert 18/08/2018 - 07:08
So you can see I have copied the names of local and auth shops as they are in column N of Form Responses 1 sheet
User 18/08/2018 - 07:08
yes., then
Excelchat Expert 18/08/2018 - 07:09
Under Local Workshop(B3 cell) I have put the formula to count from column N of sheet Form Responses 1on cell B4 of Sheet1
Excelchat Expert 18/08/2018 - 07:10
The formula is ....=countif(range of col N, the name to count)
Excelchat Expert 18/08/2018 - 07:10
in excels language which is.....=COUNTIF('Form Responses 1'!\$N\$3:\$N\$21,"="&B3)
User 18/08/2018 - 07:10
got it.,
Excelchat Expert 18/08/2018 - 07:11
in the fiormula....'Form Responses 1'!\$N\$3:\$N\$21....is the range from where we are counting
User 18/08/2018 - 07:11
then how to make the chart?
Excelchat Expert 18/08/2018 - 07:11
and "="&B3....is that we are counting the name which is equal to B3 of sheet1...which is Local....
Excelchat Expert 18/08/2018 - 07:11
Ok now we have got Local and Auth shop numbers
Excelchat Expert 18/08/2018 - 07:12
now select the cells B3:C4 by mouse cursor
Excelchat Expert 18/08/2018 - 07:12
Click on "insert" tab
Excelchat Expert 18/08/2018 - 07:13
Then click on "Insert Column Chart"
Excelchat Expert 18/08/2018 - 07:13
That should do
Excelchat Expert 18/08/2018 - 07:13
ok?
User 18/08/2018 - 07:15
can u see my writing?
Excelchat Expert 18/08/2018 - 07:15
where?
User 18/08/2018 - 07:15
in the excel space right to the chat box
Excelchat Expert 18/08/2018 - 07:15
no
Excelchat Expert 18/08/2018 - 07:16
yeah saw now
Excelchat Expert 18/08/2018 - 07:16
ok so first lets say you are focusing on range G1 through G10
User 18/08/2018 - 07:16
ok
Excelchat Expert 18/08/2018 - 07:17
and we need to know how many times A which is on cell I1 is there on G1 to G10
User 18/08/2018 - 07:17
ok.,
Excelchat Expert 18/08/2018 - 07:17
so you have wrote the function on A1
Excelchat Expert 18/08/2018 - 07:18
now lets select together rest
User 18/08/2018 - 07:18
ok
Excelchat Expert 18/08/2018 - 07:18
First I have cleared all except the function in A1\
Excelchat Expert 18/08/2018 - 07:18
check
User 18/08/2018 - 07:19
ok
User 18/08/2018 - 07:19
checked
Excelchat Expert 18/08/2018 - 07:19
Now, place your cursor inside the function by double clicking over A1
Excelchat Expert 18/08/2018 - 07:19
Done?
User 18/08/2018 - 07:19
done
Excelchat Expert 18/08/2018 - 07:20
now while you are on A1 and the cursor is blinking inside the function countif()....place your mouse over G1 and drag upto G10
Excelchat Expert 18/08/2018 - 07:20
Done?
User 18/08/2018 - 07:20
yeah
User 18/08/2018 - 07:20
done
Excelchat Expert 18/08/2018 - 07:21
Then put a comma next
User 18/08/2018 - 07:21
,then
Excelchat Expert 18/08/2018 - 07:21
No after G10...put a comma
User 18/08/2018 - 07:21
yeah yeah.,
User 18/08/2018 - 07:21
User 18/08/2018 - 07:21
go on
Excelchat Expert 18/08/2018 - 07:22
After the comma...write.."="&I1
Excelchat Expert 18/08/2018 - 07:22
Then press enter
Excelchat Expert 18/08/2018 - 07:23
Now see..
User 18/08/2018 - 07:23
yeah done :-)
Excelchat Expert 18/08/2018 - 07:23
When we are changing any of The A within G1 to G10...the count is changing
User 18/08/2018 - 07:24
the I1 is for to source the keyword., ryt?
Excelchat Expert 18/08/2018 - 07:24
Yes
User 18/08/2018 - 07:24
understood :-)
Excelchat Expert 18/08/2018 - 07:24
Check now we are checking for B
User 18/08/2018 - 07:24
thanks a lot bro :-)
Excelchat Expert 18/08/2018 - 07:24
most welcome!
Excelchat Expert 18/08/2018 - 07:25
If you are leaving please give me ratings after ending manually from your end!
User 18/08/2018 - 07:25
so we must follow this step before making the chart., ryt?
User 18/08/2018 - 07:25
sure sure., but i'm not over yet
Excelchat Expert 18/08/2018 - 07:26
yes...have the number of times Local and Auth shops are there and then selecting those cells as I just did in Sheet 1
User 18/08/2018 - 07:26
ok ok
Excelchat Expert 18/08/2018 - 07:26
Look carefully in our case the range and Source key was in different sheets
User 18/08/2018 - 07:27
that's i can understand
User 18/08/2018 - 07:27
i can do it myself now
Excelchat Expert 18/08/2018 - 07:27
Great!
User 18/08/2018 - 07:27
but, in the file i sent i need convert every columns into rows, and rows into columns
User 18/08/2018 - 07:27
is it possible?
User 18/08/2018 - 07:28
oh., yes; then how to do it for different sheet?
Excelchat Expert 18/08/2018 - 07:29
For a different sheet just select the range from the sheet we need to have the count from
User 18/08/2018 - 07:30
oh., that's all?
User 18/08/2018 - 07:30
it's great :-)
Excelchat Expert 18/08/2018 - 07:31
Yes, you can do that the same way you did for the same sheet....just going to the sheet we need to have the count from
Excelchat Expert 18/08/2018 - 07:31
You can try that
User 18/08/2018 - 07:31
ok ok., fyn
Excelchat Expert 18/08/2018 - 07:31
As I have put the data now on Sheet 2 of the shared view
User 18/08/2018 - 07:31
Excelchat Expert 18/08/2018 - 07:32
About that as that is a different type of question please post about that specifying on that part again, as we are not allowed to go for different types of question in the same session, sorry!
User 18/08/2018 - 07:33
oh., it's okay
Excelchat Expert 18/08/2018 - 07:33
Thanks for understanding!
User 18/08/2018 - 07:34
so let's go on with this
User 18/08/2018 - 07:34
let me do that this time
Excelchat Expert 18/08/2018 - 07:34
sure go on
User 18/08/2018 - 07:39
ok?
Excelchat Expert 18/08/2018 - 07:39
Yes perfect
Excelchat Expert 18/08/2018 - 07:40
You can try placing the chart now
Excelchat Expert 18/08/2018 - 07:40
You have 4 categories now
Excelchat Expert 18/08/2018 - 07:40
So there will be 4 bars
User 18/08/2018 - 07:41
u haven't taught me yet how to do that
Excelchat Expert 18/08/2018 - 07:42
Select from B4 to E5 of HSeet 2
Excelchat Expert 18/08/2018 - 07:42
*Sheet 2
Excelchat Expert 18/08/2018 - 07:42
Done?
User 18/08/2018 - 07:42
yes
Excelchat Expert 18/08/2018 - 07:42
B4 to E5
User 18/08/2018 - 07:43
yesh
User 18/08/2018 - 07:43
done
Excelchat Expert 18/08/2018 - 07:43
Check my selection...(Blue marks)
Excelchat Expert 18/08/2018 - 07:43
Now press insert
User 18/08/2018 - 07:43
mmm., then?
Excelchat Expert 18/08/2018 - 07:44
then chart
User 18/08/2018 - 07:44
then
Excelchat Expert 18/08/2018 - 07:44
Thats it
Excelchat Expert 18/08/2018 - 07:44
you have the chart, right?
User 18/08/2018 - 07:44
yes., i have
Excelchat Expert 18/08/2018 - 07:45
So thats it, right?
User 18/08/2018 - 07:45
don't we have to mention which one is the lable and which one it data?
Excelchat Expert 18/08/2018 - 07:45
You can ofcourse, those are the optional of chart
Excelchat Expert 18/08/2018 - 07:46
just right click on the chart
Excelchat Expert 18/08/2018 - 07:46
The on Chart axis& titles you will find all
User 18/08/2018 - 07:46
oh., so it just takes the upper row as lable., ryt?
Excelchat Expert 18/08/2018 - 07:46
Yes
Excelchat Expert 18/08/2018 - 07:46
A, B, C and D
User 18/08/2018 - 07:47
ok., got it :-)
User 18/08/2018 - 07:48
u can assist me only on this particular topic., ryt?
Excelchat Expert 18/08/2018 - 07:49
Yes, besides the time is almost run out
Excelchat Expert 18/08/2018 - 07:49
User 18/08/2018 - 07:50
ok., but the probability of getting you again is too low., ryt?
Excelchat Expert 18/08/2018 - 07:50
May be so, we all have to bid to get a client, so it varies
User 18/08/2018 - 07:51
mmm., ok,.
User 18/08/2018 - 07:51
anyways thank you so much for ur help
Excelchat Expert 18/08/2018 - 07:51
But all the experts are efficient , so dont worry!
User 18/08/2018 - 07:51
Excelchat Expert 18/08/2018 - 07:51
Most welcome and thanks for being so patient
User 18/08/2018 - 07:52
but nobody could help
User 18/08/2018 - 07:52
u r the one
User 18/08/2018 - 07:52
only one
Excelchat Expert 18/08/2018 - 07:52
I will take that as a compliment then! Haha!
User 18/08/2018 - 07:52
gave me the solution
User 18/08/2018 - 07:52
ha ha
User 18/08/2018 - 07:52
ok
User 18/08/2018 - 07:52
bye
User 18/08/2018 - 07:53
it's gng to end
Excelchat Expert 18/08/2018 - 07:53
Bye and have a nice time ahead
Excelchat Expert 18/08/2018 - 07:53
hope to see you soon
User 18/08/2018 - 07:53
:-)

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.