How do I combine AverageIF or CountIf with multiple Paramaters?

=AVERAGEIF((Priority:Priority="EMERGENCY", [Days to complete]:[Days to complete],>=TODAY(-30))

I would like to count or average the numbert of entries with an "Emergency" priority status from the last 30 days. How do I get my function to work?

I've tried this, but it always returns "0" which is incorrected:

=COUNTIFS(Priority:Priority, "EMERGENCY", [Date Complete]:[Date Complete], >=TODAY(-60))

Find more posts tagged with

Accepted answers

All comments

Alex Carter

=COUNTIFS([Date Complete]1:[Date Complete]20, >=TODAY(-31), Priority1:Priority20, ="Emergency")

The only difference I see is that I specified a range for my date complete range. This ended up working for me.

VBJBC

Thank you Alex,

This still doesn't quite work on my data set. It still returns "0" or "Invalid Operation" error when it should be returning at least "1" or more. I have about 470 rows worth of entries I'm accounting for. Is there a way to widen this range without getting an "Invalid Operation" error?

Paul Newcome

Are you able to provide screenshots of the formulas in the sheet?

Where exactly are you putting the formulas in relation to the data?

Have you double checked that the [Date Complete] column is in fact a date type column and the data in it is actually being stored as dates and not text strings that just look like dates?

VBJBC

Hi Paul,

All dates columns are date type columns. Below are screenshots of my Data: I am trying to combine the Average Days to Complete (Emergency) with a constraint of 30-60 days. Similar to that of completed within 14 days but have the priority specified [emergency, high, medium, low]

Screenshot 2022-07-19 100924.png

Screenshot 2022-07-19 100852.png

Screenshot 2022-07-19 100822.png

Paul Newcome

I don't see any entries in your screenshots where a row meets the criteria of the formula. Are you able to create a filter that has the same ranges and criteria and apply it to see what happens?

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.