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

jasmine.pena

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?

heyjay

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

=COUNTIFS(
[_date]:[_date], >= DATE(2024, 12, 31), 
[_date]:[_date], <= DATE(2025, 1, 1)
)

jasmine.pena

https://community.smartsheet.com/discussion/comment/416022#Comment_416022

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

=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.

jasmine.pena

@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.

jasmine.pena

@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

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)

jasmine.pena

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

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))

jasmine.pena

@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!