# COUNTIFS Formula Question

Options
✭✭

Hello,

I am trying to create a COUNTIFS formula to determine how many stops by Sales Rep, By County, By State. I have created helper columns listing the counties in each state. The formula I have is:

=COUNTIFS(County:County, [Mark Counties]@row, State:State, "OK", Date:Date, YEAR(@cell) = YEAR(TODAY()))

It is pulling a count of all counties and not limiting by state so I am getting a higher number.

My goal is to show a graph (Pie Chart) showing % time spent by market. Any ideas?

Main Data Source

Helper Column:

• ✭✭✭✭✭✭
Options

What about a filter that matches the COUNTIFS range/criteria sets?

• ✭✭
Options

Found it! Duplicate county listed in the helper column, so the total count 59 for that county was being added to the total 2 times!

Thank you Paul!

• ✭✭✭✭✭
Options

Is there a reason our formula only references "OK" and not MARKST@row?

Smartsheet Solutions Architect

• ✭✭
Options

MARKST is not the limiting factor, that is just the helper column. My hope was to count the number of times the county name pops up, if the salesman is Mark, and the state is in this case OK. The formula produces a result either way, but the count is off.

Mark has 473 stops and 19 of them are missing County so those should not be counted, but I get a result of 513 with this formula:

=COUNTIFS(County:County, [Mark Counties]@row, State:State, MARKST@row, Date:Date, YEAR(@cell) = YEAR(TODAY()))

and this one:

=COUNTIFS(County:County, [Mark Counties]@row, State:State, "OK", Date:Date, YEAR(@cell) = YEAR(TODAY()))

Could the blanks be throwing it off?

• ✭✭✭✭✭✭
Options

How are you determining what the accurate count SHOULD be?

• ✭✭
Options

Hi Paul,

I have a report set up with a summary showing the count based on created date

I also exported the sheet to Excel and did a count of all Rows containing a County entered by Mark Stevens and it comes to 454.

• ✭✭✭✭✭✭
Options

• ✭✭
Options

Hi Paul, there are no filters. It is grouped by Your Name and sorted by created.

• ✭✭✭✭✭✭
Options

In that case you shouldn't expect the number coming from the report to be the same as the COUNTIFS because the COUNTIFS has additional "filters" in it such as county and state.

• ✭✭
edited 08/23/23
Options

My issue is that I physically counted the entries made by mark and the # being returned is higher than the physical number of entries. How can my formula be producing a higher result?

I am thinking that there might be duplicate county names from different states. Could those duplicates be being counted multiple times and should formula be adjusted to include an AND statement?

• ✭✭✭✭✭✭
Options

Have you tried applying a filter to the sheet itself?

• ✭✭
Options

Hello Paul,

Yes, I get 473 entries by Mark when applying the filter from the sheet to just Mark.

There has to be something wrong with the formula or something in the background. There cannot be a higher result than the number of entries if the limiting factors are working correctly. I just don't know yet what I am missing.

=COUNTIFS(County:County, [Mark Counties]@row, State:State, MARKST@row, Date:Date, YEAR(@cell) = YEAR(TODAY()))

• ✭✭✭✭✭✭
Options

And what exactly are the filter settings?

• ✭✭
Options
• ✭✭✭✭✭✭
Options

What about a filter that matches the COUNTIFS range/criteria sets?

• ✭✭
Options

Found it! Duplicate county listed in the helper column, so the total count 59 for that county was being added to the total 2 times!

Thank you Paul!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

For future reference... A lot of times I will start with a sheet filter that replicates my range/criteria sets. If that gets tough to manage in a sheet filter (lots of AND and OR functions), then I will create an IF statement and drop it into a checkbox column to filter on.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!