# Excel - COLUMN Function Problem - Expert Solution

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

Greetings, I am trying to find a formula in excel that will return the number of students attending courses. My data is on one sheet and my report will be on another sheet. The report is formated with the students list in a coulmn (far left), the courses are list in a row (top). My data is formatted with all columns, courses in column in "A" and students in column "B". Thanks
Solved by E. C. in 27 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 31/07/2018 - 04:25
Other
Excelchat Expert 31/07/2018 - 04:25
Hello, I understand that you need help in counting the number of students attending courses, right?
User 31/07/2018 - 04:25
am i able to upload my workbook to this?
Excelchat Expert 31/07/2018 - 04:26
Yes please, that would help us a lot.
Excelchat Expert 31/07/2018 - 04:26
Please use the clip icon next to this chat.
User 31/07/2018 - 04:27
Don't see the clip icon
Excelchat Expert 31/07/2018 - 04:27
It's the green icon right next to where you are typing your message.
User 31/07/2018 - 04:27
duh
Excelchat Expert 31/07/2018 - 04:27
User 31/07/2018 - 04:27
stand by
User 31/07/2018 - 04:30
User 31/07/2018 - 04:30
hope this worked
Excelchat Expert 31/07/2018 - 04:30
Thank you. Got it.
Excelchat Expert 31/07/2018 - 04:31
I have your file. Sheet1 and Attendees
User 31/07/2018 - 04:31
yes
User 31/07/2018 - 04:31
sheet 1 is my data
User 31/07/2018 - 04:32
Attendees is where i want my data to report on
Excelchat Expert 31/07/2018 - 04:32
Okay, can you give me an example please?
User 31/07/2018 - 04:32
coulmn B is not needed on sheet1
Excelchat Expert 31/07/2018 - 04:33
Okay, can you give me an example please?
User 31/07/2018 - 04:33
I would like to know which NF, ex. Deschutes NF took D-310
User 31/07/2018 - 04:34
and so on for each of the courses listed at the top of the Attendees sheet
Excelchat Expert 31/07/2018 - 04:34
Okay, I'm not very familiar with your data so you're gonna have to be more specific. What are NF?
Excelchat Expert 31/07/2018 - 04:35
And what are D-310?
User 31/07/2018 - 04:35
National Forest
Excelchat Expert 31/07/2018 - 04:35
User 31/07/2018 - 04:35
D-310 is a course
Excelchat Expert 31/07/2018 - 04:35
Let's use an actual example.
User 31/07/2018 - 04:36
the courses are listed in coulmn A on sheet1
Excelchat Expert 31/07/2018 - 04:36
Let's use C3 in the Attendees sheet.
User 31/07/2018 - 04:36
coulmn C in sheet one refers to an Agency
Excelchat Expert 31/07/2018 - 04:36
Columbia River Gorge NSA and D-310 Support Dispatcher intersect in C3
User 31/07/2018 - 04:36
ok, yes
Excelchat Expert 31/07/2018 - 04:37
So you want to know how many attendees from Columbia River Gorge NSA attended D-310 Support Dispatcher?
User 31/07/2018 - 04:37
Eaxctly
Excelchat Expert 31/07/2018 - 04:37
D-310 Support Dispatcher is found in column A of Sheet1
Excelchat Expert 31/07/2018 - 04:37
Which column will Columbia River Gorge NSA be?
User 31/07/2018 - 04:37
correct
User 31/07/2018 - 04:38
C
User 31/07/2018 - 04:38
of sheet1
Excelchat Expert 31/07/2018 - 04:38
Okay, thank you for clarifying that. I can help you with this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
User 31/07/2018 - 04:39
ok
Excelchat Expert 31/07/2018 - 04:40
Excelchat Expert 31/07/2018 - 04:41
I used the COUNTIFS formula to generate the count of attendees attending a certain course per location.
User 31/07/2018 - 04:42
Excelchat Expert 31/07/2018 - 04:42
Are you getting an error?
User 31/07/2018 - 04:42
yes
Excelchat Expert 31/07/2018 - 04:42
May I know what the error is?
User 31/07/2018 - 04:42
"Something went wrong, please try again."
Excelchat Expert 31/07/2018 - 04:43
Just try again.
Excelchat Expert 31/07/2018 - 04:43
Make sure you are clicking the right button.
Excelchat Expert 31/07/2018 - 04:43
Here's the file again.
User 31/07/2018 - 04:44
weird
User 31/07/2018 - 04:44
I'll keep trying
Excelchat Expert 31/07/2018 - 04:44
Excelchat Expert 31/07/2018 - 04:44
User 31/07/2018 - 04:45
didn't open becasue I was in Internet Explorer
User 31/07/2018 - 04:45
opened with Mozilla
Excelchat Expert 31/07/2018 - 04:46
Excelchat Expert 31/07/2018 - 04:46
Try that if it still won't work.
Excelchat Expert 31/07/2018 - 04:47
If that still won't work then just apply this formula in cell C3 of your Attendees
Excelchat Expert 31/07/2018 - 04:47
=COUNTIFS(Sheet1!\$A:\$A,Attendees!C\$1,Sheet1!\$C:\$C,Attendees!\$B3)
Excelchat Expert 31/07/2018 - 04:47
Then drag or copy it to the other cells.
User 31/07/2018 - 04:47
opened it but the formula did not work for BLM, FWS, NPS or ODF
Excelchat Expert 31/07/2018 - 04:47
What are those? Please specificy. I'm not very familiar with your data.
User 31/07/2018 - 04:48
The are other Agencies
Excelchat Expert 31/07/2018 - 04:48
Please reference the cells that aren't working.
User 31/07/2018 - 04:48
on the attendees sheet
Excelchat Expert 31/07/2018 - 04:48
Which among the BLM are you expecting to have values?
Excelchat Expert 31/07/2018 - 04:49
Ah, I understand now. It's because your BLM Attendees have an extra space at the end.
Excelchat Expert 31/07/2018 - 04:49
If you double-click on the attendees like BLM, Burns you'll notice an extra space at the end.
User 31/07/2018 - 04:49
Ahhhhh
User 31/07/2018 - 04:49
AWESOME!!!
User 31/07/2018 - 04:50
Thank you so much!
Excelchat Expert 31/07/2018 - 04:50
You can use this formula instead:
Excelchat Expert 31/07/2018 - 04:50
=COUNTIFS(Sheet1!\$A:\$A,TRIM(Attendees!C\$1),Sheet1!\$C:\$C,TRIM(Attendees!\$B3))
Excelchat Expert 31/07/2018 - 04:50
Paste that in C3 then apply it to the rest.
Excelchat Expert 31/07/2018 - 04:50
Unless you've already removed the extra space at the end.
User 31/07/2018 - 04:50
YOu ROCK!
Excelchat Expert 31/07/2018 - 04:50
Excelchat Expert 31/07/2018 - 04:50
Would there be anything else that I can help you with regards to the original question?
User 31/07/2018 - 04:51
Negative
User 31/07/2018 - 04:51
Thank again
Excelchat Expert 31/07/2018 - 04:51
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert 31/07/2018 - 04:51
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert 31/07/2018 - 04:51
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.

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.