Problem with SUMIFs second critera based upon date
Hello,
Smartsheet example here: https://app.smartsheet.com/b/publish?EQBCT=e6247aff028844ff9da1e4d0906860c2
What I am trying to do is the following:
=SUMIFS(Name1:Name14, "Happy", average1:average14, Date1:Date14, >TODAY(-31)) / COUNTIFS(Name1:Name14, "Happy", Date1:Date14, >TODAY(-31))
Basically the following critera:
- If the name is Happy
- If the date is within the last 30 days
- return the average sum / divide by the total count
I keep getting invalid argument. ANyone have any idea what is casuing that? If I remove the date it works just fine.
Dr. St Nicholas Burrus DHA, PMP
I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.
Comments
-
Figured it out.
=SUMIFS(average1:average14, Name1:Name14, "Happy", Date1:Date14, >TODAY(-31)) / COUNTIFS(Name1:Name14, "Happy", Date1:Date14, >TODAY(-31))
The SUM RANGE must be FIRST. Not third like in sumif.
Dr. St Nicholas Burrus DHA, PMP
I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.
-
Yep, its a little backwards! But it makes sense in the long run. This post is very helpful in identifying the layout of the different parts of a function:
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!