How to use @cell or @row in a COUNTIF formula

hbondoc

I am using the following formula:

=COUNTIFS({All Snap Data Dept Code}, ="12326", {All Snap Data Month}, "Sept")

to return the number of times a department number is used on a report in September. Easy enough, but there are almost 300 unique department codes. I tried clicking on the box with the departments (resulting in [Dept Code]@row being in the criterion1 place in the formula. However, this always results in a 0, even when there are 1 or more occurrences of that department to count.

=COUNTIFS({All Snap Data Dept Code}, [Dept Code]@row, {All Snap Data Month}, "Sept")

Is there any way to make this work so that the copy and paste down automatically references the department number (which is the column to the left of where the count formula is), rather than me having to type every single one and then copy and paste over and update the month for October, December, etc.

Both ranges are referencing a separate sheet (the same one but different columns)

Paul Newcome

How exactly are you populating the data in each of the sheets?

hbondoc

@Paul Newcome I have been tasked with converting an excel dashboard to be on smartsheet. The reference sheet was imported from excel. The dashboard is very outdated and requires a lot of manual work to keep updated every month.

KPH

@hbondoc

Your formula looks like it should do what you want to achieve. Have you solved this? If not, maybe share a screen shot of the sheet with the formula and the sheet with the data, and we can see if we can spot anything.