How can I do a count and sum formula for items that are listed between two points on a date range?

jasmine.pena
jasmine.pena ✭✭✭
edited 01/29/24 in Formulas and Functions

So I'm working on the year-over-year metrics and have figured out how to do a count formula using a one-direction date range criteria- Either <= or >=. 

Examples: =COUNTIFS({Submission Range 12}, <=DATE(2023, 12, 31), {Submission Range 3}, true) and =SUMIF({Submission Range 12}, <=DATE(2023, 12, 31), {Submission Range 11})

How can I do a count formula for items that are listed between two points on a date range? So between Dec. 31, 2024, and Jan. 1, 2025?

Additionally, how could I do a sum of values that are located between two points on a date range?

Best Answer

  • heyjay
    heyjay ✭✭✭✭✭
    Answer ✓

    Try this. I removed the extra parenthesis

    =COUNTIFS(
    {Submission Range 12}, >=DATE(2024, 12, 31), 
    {Submission Range 12}, <=DATE(2026, 1, 1),
    {Submission Range 3}, 1)
    

    =COUNTIFS({Submission Range 12}, >=DATE(2024, 12, 31), {Submission Range 12}, <=DATE(2026, 1, 1),({Submission Range 3}, 1))

    ...

Answers

  • heyjay
    heyjay ✭✭✭✭✭
    edited 01/26/24
    =COUNTIFS(
    [_date]:[_date], >= DATE(2024, 12, 31), 
    [_date]:[_date], <= DATE(2025, 1, 1)
    )
    

    ...

  • I'm not sure if I do not understand your recommendation or if I simply did something incorrect. I came up with this: =COUNTIFS({Submission Range 12}, >=DATE(2024, 12, 31), <=DATE(2025, 1, 1)) and receive an #INVALID OPERATION error.

  • heyjay
    heyjay ✭✭✭✭✭
    edited 01/26/24
    =COUNTIFS(
    {Submission Range 12}, >=DATE(2024, 12, 31), 
    {Submission Range 12} ,<=DATE(2025, 1, 1)
    )
    
    

    Looks like you missed the second range.

    =COUNTIFS({Submission Range 12}, >=DATE(2024, 12, 31), {Submission Range 12}, <=DATE(2025, 1, 1))

    You sure you are really counting 12/31/24 - 1/1/25? That is just one day difference.

    ...

  • @heyjay That worked perfectly. Thank you!

    Also thank you for catching my date error. For this formula, I was setting up to capture future information (days in 2024.) I've corrected the end date value to 2026.

  • @heyjay To build off of that formula using the same reference sheet I am trying to to count the number of items in that date range which also have the completed box checked. I am receiving the #UNPARSEABLE error with the below formula. What I am missing/doing wrong here? :D

    =COUNTIFS({Submission Range 12}, >=DATE(2024, 12, 31), {Submission Range 12}, <=DATE(2026, 1, 1) AND({Submission Range 3}, checked))

    After that, I used the same base formula to help build one that I need to calculate the sum of a value column referencing the same sheet. I am receiving the #INVALID OPERATION error message on this one. Can you help with this one too?

    =SUMIFS({Submission Range 12}, >=DATE(2024, 12, 31), {Submission Range 12}, <=DATE(2026, 1, 1), ({{Submission Range 11})).

  • heyjay
    heyjay ✭✭✭✭✭

    For when you want to include the check boxes. Check is "1", unchecked is "0"

    
    =COUNTIFS(
    {Submission Range 12}, >=DATE(2024, 12, 31),
    {Submission Range 12}, <=DATE(2026, 1, 1),
    ({Submission Range 3}, 1))
    
    


    For the SUMIFs, the first arguement is the column you want to SUM. And the following Pairs will be the criterion.

    =SUMIFS(
    {Submission Range 11}
    {Submission Range 12}, >=DATE(2024, 12, 31), 
    {Submission Range 12}, <=DATE(2026, 1, 1)
    

    ...

  • I was able to get the SUMIFS formula to work.

    For the COUNTIFS formula. I've used True on other formulas for the check box and it worked. I did try to make this change here though, but still received the #UNPARSEABLE error.

    It now look like this =COUNTIFS({Submission Range 12}, >=DATE(2024, 12, 31), {Submission Range 12}, <=DATE(2026, 1, 1),({Submission Range 3}, 1))

  • heyjay
    heyjay ✭✭✭✭✭
    Answer ✓

    Try this. I removed the extra parenthesis

    =COUNTIFS(
    {Submission Range 12}, >=DATE(2024, 12, 31), 
    {Submission Range 12}, <=DATE(2026, 1, 1),
    {Submission Range 3}, 1)
    

    =COUNTIFS({Submission Range 12}, >=DATE(2024, 12, 31), {Submission Range 12}, <=DATE(2026, 1, 1),({Submission Range 3}, 1))

    ...

  • @heyjay That worked!! Thank you so much for all of your help working through that!! I greatly appreciate your assistance! 🏆️💐 First place goes to you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!