Countif Formula help!

I am looking to get totals for each employee (in the last 182 days) for each error type up in the master section but my formula isnt quite working

=COUNTIFS(Staff:Staff, @cell = Staff@row, Date:Date, Error:Error, @cell >= TODAY(-182))

what am i missing?

Find more posts tagged with

Accepted answers

All comments

Razetto

@ldavenportHAP I suggest creating a separate sheet for the metrics, indent each error under each staff as below. Your formula had the date criteria at the end instead of right after the date column reference. Try something like this =COUNTIFS(Staff:Staff, @cell = "Jane Doe", Date:Date, @cell >= TODAY(-182), Error:Error, @cell = "Failed Demo")

ldavenportHAP

@Razetto thanks, but my company would really rather all totals and data to remain on one sheet. We just want totals at the top broken down by staff/leaders and occurrences at the bottom. Theyve asked for an additional total of all occurrences for each member which i figured out. I suggested this be done with a report but they find the below more aesthetically pleasing. I just need to figure out the formula for totaling up for each staff member by error (which i manually entered below for show)

Paul Newcome

You have two ranges mixed up in the middle and forgot your criteria for one of them.

You have

Date Range, Error Range, Date Criteria

You need

Date Range, Date Criteria, Error Range, Error Criteria

ldavenportHAP

@Paul Newcome

thank you!

is there a way to simplify the formula to work as a column formula?

So staff count if staff @ row and Error @ row rather than the below

=COUNTIFS(Staff:Staff, @cell = "Jane Doe", Date:Date, @cell >= TODAY(-182), Error:Error, @cell = "Dropped Payment")

Paul Newcome

Yes. You would do it pretty much how you have it.

Instead of "Jane Doe" you would use Staff@row and instead of "Dropped Payment" you would use Error@row.

ldavenportHAP

Thank you again @Paul Newcome ! you're such a wonderful help!

It is however not counting so i am not sure what i am missing

i have two dropped payments shown

but it isnt counting them

Formula i have is =COUNTIFS(Staff:Staff, @cell = "Staff@Row", Date:Date, @cell >= TODAY(-182), Error:Error, @cell = "Error@row")

Paul Newcome

Try removing the quotes from around the cell references.

ldavenportHAP

@Paul Newcome when i do that it tells me the syntax isnt quite right

Paul Newcome

Sorry for missing it the first time.

@row is case sensitive. Your first one has a capital R, but it needs to be all lower case.

ldavenportHAP

@Paul Newcome that worked! thank you so much for your help!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.