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
[Uploaded an Excel file]
User
31/07/2018 - 04:27
stand by
User
31/07/2018 - 04:30
[Uploaded an Excel file]
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
I mean, in your data.
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
Please download this file.
[Uploaded an Excel file]
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
Ahh, it won't let me download
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.
[Uploaded an Excel file]
Excelchat Expert
31/07/2018 - 04:43
Here's the file again.
[Uploaded an Excel file]
User
31/07/2018 - 04:44
weird
User
31/07/2018 - 04:44
I'll keep trying
Excelchat Expert
31/07/2018 - 04:44
Can you try this instead:
Excelchat Expert
31/07/2018 - 04:44
https://drive.google.com/open?id=1jGv4W8X5Io4ys7j6EmVeAWMIcgliNQAI
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
https://drive.google.com/open?id=1Zjoc8MVxaOORm7sLiOI25Wg-XFzbvFxz
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
Glad to have helped!
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.
