SUMIFS within Date Range

Options

Hi,

I need help creating a formula to summarize an average using Sheet Summary Columns,

I have the following sheet columns: Current Date, Average Rating,

I have the following summary columns: From Date, To Date

I have the following sheet summary column (locked) for our formula: Total Average Rating

I'm trying that the From Date and To Date columns should indicate from when until when they want the formula to calculate based on the dates in the Current Date column.

I tried something like this, but I got the #Unparseable error:

=SUMIF([Current Date]:[Current Date], =[From Date]#:[To Date]#,[Total Average Rating]:[Total Average Rating]

Best Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 06/08/22 Answer ✓
    Options

    @A Rose

    Let's start with logic and syntax. You want this formula to add up the values in the [Average Rating] column for rows where the [Current Date] value is between the [From Date]# summary column value and the [To Date]# summary column value, yes?

    If so, you need to utilize greater than/less than and use the two date summary fields as separate criteria; we can do that by adding the AND function. You also listed your summary field as the range to add up, I think you meant to put the range for the Average Rating column:

    =SUMIF([Current Date]:[Current Date], AND(@cell >= [From Date]#, @cell <= [To Date]#), [Average Rating]:[Average Rating])

    In English: Add up the values in the Average Rating column where the Current Date value is greater than or equal to the value in the From Date summary field, and also less than equal to the value in the to Date summary field.

    Give that a shot.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    I was wondering about that. You want the average of all the averages. Gotcha. That's a different function: AVERAGEIF

    You'll have to try it an see if it works the same. It looks like it does, but like many things in Smartsheet, your mileage may vary!

    =AVERAGEIF([Current Date]:[Current Date], AND(@cell >= [From Date]#, @cell <= [To Date]#), [Average Rating]:[Average Rating])

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • A Rose
    A Rose ✭✭✭✭
    Answer ✓
    Options

    Changed the beginning with AVERAGEIF, thanks for your help!!

    =AVERAGEIF([Current Date]:[Current Date], AND(@cell >= [From Date]#, @cell <= [To Date]#), [Average Rating]:[Average Rating])

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 06/08/22 Answer ✓
    Options

    @A Rose

    Let's start with logic and syntax. You want this formula to add up the values in the [Average Rating] column for rows where the [Current Date] value is between the [From Date]# summary column value and the [To Date]# summary column value, yes?

    If so, you need to utilize greater than/less than and use the two date summary fields as separate criteria; we can do that by adding the AND function. You also listed your summary field as the range to add up, I think you meant to put the range for the Average Rating column:

    =SUMIF([Current Date]:[Current Date], AND(@cell >= [From Date]#, @cell <= [To Date]#), [Average Rating]:[Average Rating])

    In English: Add up the values in the Average Rating column where the Current Date value is greater than or equal to the value in the From Date summary field, and also less than equal to the value in the to Date summary field.

    Give that a shot.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • A Rose
    A Rose ✭✭✭✭
    Options

    Hi @Jeff Reisman,

    Thanks so much for that! seems that we're getting closer to what I needed, so now it looks like it sums it up with an average, but the total number is like 110, when the actual average numbers in the Average Rating column are ranging with about 4.5 3.2 etc. how can we average it out better?

    Thanks!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    I was wondering about that. You want the average of all the averages. Gotcha. That's a different function: AVERAGEIF

    You'll have to try it an see if it works the same. It looks like it does, but like many things in Smartsheet, your mileage may vary!

    =AVERAGEIF([Current Date]:[Current Date], AND(@cell >= [From Date]#, @cell <= [To Date]#), [Average Rating]:[Average Rating])

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • A Rose
    A Rose ✭✭✭✭
    Answer ✓
    Options

    Changed the beginning with AVERAGEIF, thanks for your help!!

    =AVERAGEIF([Current Date]:[Current Date], AND(@cell >= [From Date]#, @cell <= [To Date]#), [Average Rating]:[Average Rating])

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!