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.

Find more posts tagged with

Formulas

Comments

Nick Burrus

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.

Mike Wilday

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:

https://help.smartsheet.com/functions

Sign in to join the conversation!

Quick Links

Trending discussions

Calculating Quarters with a Start and a Finish date
I searched the community and did not see a formula that calculated Quarter based on a Start and a Finish date while also accounting for cases where the year for the Start and Finish date differ. The formula below handles that. A few notes … It assumes that Finish is always >= Start. The entire formula is wrapped with…
Circular Reference with formula
i am not good with writing formulas within a sheet. I asked the AI tool in the sheet to help me write a formula and I am getting a Circular reference back. I'm trying to have it write the following formula for me. If the column "Tracking #" is not blank, change Column "Deployment Phase" to "Shipped" I have tried adding in…
How to delay the execution of a workflow whose trigger occurred much earlier?
I have a workflow that detects a row(date) that is changing its Fiscal Week Finish Date and send this row to other Sheet. Long story short: I collect delayed tasks in separate file & I tag reasons for delay for these tasks. It happens that plan are analyzed 2-3 hours and dates are being changed several times. I would like…