COUNTIFS Formula Question

CFT

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:

Paul Newcome

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

CFT

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!

Ramzi

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

CFT

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?

Paul Newcome

How are you determining what the accurate count SHOULD be?

CFT

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.

Paul Newcome

What are your report filters?

CFT

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

Paul Newcome

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.

CFT

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?

Paul Newcome

Have you tried applying a filter to the sheet itself?

CFT

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()))

Paul Newcome

And what exactly are the filter settings?

CFT

Paul Newcome

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

CFT

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!

Paul Newcome

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.