# Problem with SUMIFs second critera based upon date

Options
✭✭✭✭✭✭
edited 12/09/19

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.

Tags:

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!