Countif Reference Equals Name @ Row or CONTAINS

Michelle Fayed

Good morning all. I am trying to create a COUNTIF formula that counts the amount of times a station is listed on the reference sheet if it equals the name at row. I have tried the following two formulas which return 0 when they should be returning the number in the 2022 column:

=COUNTIFS({Station Count}, CONTAINS(Name@row, @cell))

=COUNTIF({Station Count}, Name@row)

I have a list of over 100 names which need to be counted and a formula to change annually would be much appreciated instead of changing every single " ".

Andrée Starå

Hi @Michelle Fayed

I hope you're well and safe!

Have you explored using a Report instead?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Michelle Fayed

@Andrée Starå So grateful for your response. All is well thank you!!

I am pulling this into a sheet because I have another formula column that is pulling the 3 highest numbers for me from the column you see. I am open to a new way of doing that?

Paul Newcome

Are you able to provide screenshots of the source data that shows the range you are trying to count?

Michelle Fayed

@Paul Newcome as to my initial question on this post, I am looking to count the amount of times a station number appears on the sheet below based on the cell name in either the primary column or the name column.

This then retries the number which is currently in the 2022 column, as seen in the image below. Once that is populated I am using the shown formula to identify the 3 largest numbers which my column 2022 is currently providing.

Does this help? I am so grateful for the help

Paul Newcome

Exactly what is covered by this range?

{Station Count}

Michelle Fayed

@Paul Newcome I apologize, I am a new smartsheeter. What do you mean?

Paul Newcome

@Michelle Fayed In the formulas in your original post you have a cross sheet reference. Exactly what is that pointing to? What exactly is the formula set up to count?

Michelle Fayed

@Paul Newcome

The formula in my original post is referencing the column titled Station # in the image below:

I trying to set up the formula to count all of the time the station listed in the names column is listed in the Station # column on the referenced sheet. Both columns properties are set to text and the stations are listed exactly the same, without exactly spaces etc.

So grateful for your reply and for helping me!

Paul Newcome

Hmm... Ok. Is there a reason you are using "Name@row" in your COUNTIFS instead of referencing the Primary Column?

Michelle Fayed

@Paul Newcome I've done it both ways with the same result! I just need it to reference one of those columns?