How to Count the Number of times a Value Appears in a Column in Another Sheet

bhammond

I want to collect some data to populate a dashboard with. Essentially, I am trying to get a count of how many times a certain type of event occurred this year. I am trying to use the countif formula to pull data from a column (column name: "Event Type") in another sheet (sheet name: "On-Site Event Request") and, for example, count the number of times that the word "Networking" is in that column. What should this formula look like?

Kelly Moore

Hey @bhammond

You will need cross sheet (which means data resides in another sheet) references. These you will have to manually create, you cannot simply copy paste these into your sheet

Your formula will look like this

=COUNTIFS({your source sheet Event Type column reference}, "Networking")

Note: if "Networking" is in a row in your target sheet, you can simply refer to the column in your target sheet Event Type and the the formula would be dynamic vs hard coding "Networking" into this formula

=COUNTIFS({your source sheet Event Type column reference}, [Event Type]@row)

Does this make sense to you?

Kelly

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

bhammond

Hi Kelly, Thanks for responding.

I am using the reference another sheet function to select the column in my source sheet and this is the formula I am using =COUNT(Formula1:Formula1{On-Site Event Request Range 3}, "Networking") and I am still getting an error. Any idea what I am doing wrong?

Genevieve P.

Hi @bhammond

It looks like you may have a reference at the front of the formula that isn't needed.

You'll also need COUNTIFS instead of just COUNT because you have criteria to look for 🙂

Try this:

=COUNTIFS({On-Site Event Request Range 3}, "Networking")

Cheers,

Genevieve

bhammond

I am using the above mentioned COUNTIFS formula and it has been working well apart from one issue. The column that it is pulling data from is a multi-select and I have noticed that if more than one item (Eg. Networking & Workshop) is selected, the formula doesn't count either of those in the total. Does anyone know of a way around this?

Genevieve P.

Hey @bhammond

Thanks for clarifying that it's multi-select! The quotes are looking for an exact match. If you need to look for one selection among others, try the HAS function to search for if that cell has a specific value along with others, like so:

=COUNTIFS({On-Site Event Request Range 3}, HAS(@cell, "Networking"))

Cheers,

Genevieve

bhammond

@Genevieve P. That worked, thank you so much!