**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.*