Help with Cross Sheet Formula to gather student count

dennis.goudy72296

Team,

I have a COUNTIF formula that I am using to grab the number of students attending a class from another sheet. I now need to add a another sheet to the formula so it counts the student attending. Hopefully, my attached PDF PPT slide explains what I am looking for. A serious big thank you to your help with this.

Dennis

Cross Sheet Reference Formula Help.pdf

dennis.goudy72296

Paul - The formula works great. As stated earlier, our community is fantastic! Thanks to all that jumped in to help me with this.

Gillian C

Hi @dennis.goudy72296

So looks like you want to change from Countif to Countifs

Try the following:

=COUNTIFS({Registration and Consent Class ID},[Class ID]@row, {Archive UFRA Student Registration Class ID},[Class ID]@row)

Hope that helps :)

dennis.goudy72296

Thanks so much. I will try this now.

dennis.goudy72296

Gillian:

Thanks so much. I am now getting an incorrect argument set message. So, I just need to make sure everything is spaced, and referenced correctly, right? I double checked my references in the formulas and all looks good. I will jump back into this later this evening to find my error.

=COUNTIFS({Registration and Consent Class ID}, [Class ID]@row, {Archive UFRA Student Registration Class ID}, [Class ID]@row)

Once again, thank you so much!

Dennis

Gillian C

Hi @dennis.goudy72296

mmm it might not like it if your ranges in the two sheets are massively different (see the link)

Formula error messages | Smartsheet Learning Center

If that is the case then you could have a column counting current students and then a column for counting archived students and then a column that sums them?

I'll keep my fingers crossed for you!

Just @ me if you come across any issues later and I'll see if I can help

dennis.goudy72296

Thanks again Gillian. I will pull some ideas together and give it a try. I really appreciate the help. This community thing is fantastic!! Way to go Smartsheet.

Gillian C

Hi @dennis.goudy72296

Glad I could help! The community is great, there is always something to learn and always someone who can help :)

Paul Newcome

The sizes of the ranges aren't the challenge this time. The error message is coming from referencing two different sheets within the same function. Since you are referencing two different sheets, you will need to write out two separate COUTNIFS (one for each sheet) and add them together.

=COUNTIFS({1st Sheet}, …………..) + COUNTIFS({2nd Sheet}, …………….)

dennis.goudy72296

Paul - I will give that one a try. It makes sense. I will let you know.

Dennis

dennis.goudy72296

Paul - The formula works great. As stated earlier, our community is fantastic! Thanks to all that jumped in to help me with this.

Paul Newcome

Happy to help. 👍️