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})
Answers
-
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!
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 143 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!