Countif - to count how many matching dates are in one sheet onto another sheet

Dan_H

New to smartsheet but thought i'd clocked how to do this.

• I have a set of dates in one column on one sheet
• On a seperate sheet (master) I have a column that counts the number of times that date is matched to a date in this sheet

I have tried the following:

=countif(sheetname); [date]@row)

=countif(sheetname); [date]@row)

=countif(sheetname); "20/01/22)

these either bring up #parseable or 0 which isnt the figure I am looking for and doesnt match the data. any tips?

Anupriya

It might be an issue of selecting the correct range in the source sheet. When you start writing the COUNTIF function and click on "Reference Another sheet" to pick the right column, make sure you click on the column name. This will select the whole column. This should help with the issue.

Genevieve P.

Hi @Dan_H

To add to @Anupriya's answer, here's an article with a quick video tutorial in how to create a reference to another sheet in a formula: Cross-sheet formulas

Cheers,

Genevieve

Dan_H

sadly neither helped in this regard.

So to be more specific. I want to do the following:

1) Count the number of cells in a specific column, that contain a certain date

2) This date is on a "master sheet" in a column

so by presumption I should be able to do a countif but its not reading it, even if I just remove the

"[column name]@row" and change it to just the specific date of the training. Any ideas?

Or is there another way around it?

Genevieve P.

Hi @Dan_H

Would you be able to post a screen capture of the sheet you're referencing and the sheet you're writing the formula in? (But block out sensitive data).

As long as the referenced column is a Date Type of column, you should be able to write something like what you had in your original post:

=COUNTIF({Date Column Other Sheet}; date@row)

If this is giving you an error, what error are you seeing?

Thanks,

Genevieve

Michelle Fayed

@Genevieve P.

I am having a similar problem. As you can see below I am using the formula you suggested but it is coming up with 0 when it should be showing the information you can see in the 2022 column. I am asking to formula to count all the times on my referenced master sheet that the station in the name@row is listed on there. Help, please?

Genevieve P.

Hi @Michelle Fayed

Two things to check here! 🙂

First of all, is it possible that the value in your other sheet is spelled a little differently? For example, if there is no space between "Station" and "2" in your other sheet - Station2, then this wouldn't be an exact match and you'll get a return of 0.

The second thing to check is the type of column you're looking into in your {Station Count} range. Is that a Multi-Select column? If so, you current formula is only searching for a cell where the value "Station 2" is the only value selected.

In this instance you'll want to use the HAS function as well, to see if the cell has that value among others:

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

Cheers,

Genevieve

Michelle Fayed

@Genevieve P. Thank you so very much for getting back to me. Both columns are text and are listed the same. Neither column is based on a formula either. Still returning 0. Any other suggestions, please?

Genevieve P.

Hi @Michelle Fayed

Can you post a picture of your {Station Count} column in the source sheet? (Click "Edit Reference" to quickly bring up a window that shows the referenced column).

Michelle Fayed

@Genevieve P.

Below is the source sheet. I am asking it to count from the Station # column.

Genevieve P.

Hi @Michelle Fayed

Thank you! The only thing I can think to check here is the {reference} itself, since all your formula outputs are 0.

The image of the sheet looks good! But can you confirm that the correct column is selected for the {Station Count} reference?

I've seen it a few times where a reference is edited and updated throughout a whole sheet, instead of a new reference being created. Is it possible that could have happened here?