Average If formula to calculate if multiple criteria is met

Trying to create a formula that will average the # of days based on the contract manager name & if the dates are for the month of June. This is the formula I'm using which works until I add the date range in. The data is in 2 different sheets.

=AVERAGEIF({CM SLA (received to opened) Range 1}, [Contract Manager]@row, {CM SLA Submit Date}, >=DATE(2024, 6, 1), {CM SLA Submit Date}, <=DATE(2024, 6, 30), {SLA Rec-Open})

Find more posts tagged with

Formulas

Accepted answers

All comments

NickStaffordPM

Hello!

Something I have done in the past to get around stuff like this is create a new column that has a formula specifically for the Date Determination in your formula.. That way, you can determine in one column (hidden or not) if the row is within an acceptible date range (if statement with value of "Yes" when you want to average it), and then in the AVERAGEIF statement you only need to have the new column referenced and the lookup value is "Yes" instead of needing to navigate the formula you are having trouble with to determine if the date is acceptible to include.

Maybe not a direct answer but hopefully provides you with an answer!

Let me know if you go down this path!

dzenus

Thanks Nick, I did what you said to a degree, I was able to add a column that basically has the month name in it but the formula isn't working. Because it's still looking for 2 criteria to be met and that seems to be causing the issue. This is the formula I've tried instead =AVERAGEIF({CM SLA Sourcing Owner}, [Contract Manager]@row, {CM SLA Sub Month}, "June", {SLA Rec-Open})

Unless I'm doing something wrong there is not AVERAGEIFS formula. Hoping maybe I'm just missing a bracket or something.

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.