Problem with SUMIFs second critera based upon date

NBurrusNBurrus ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions
05/31/19 Edited 12/09/19



Smartsheet example here:


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. 

Popular Tags:


  • NBurrusNBurrus ✭✭✭✭✭

    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 WildayMike 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:

Sign In or Register to comment.