Issue creating Summary fields for Q1-23, Q2-Q4-23

Scott B 12

Dear Community,

I am trying to create Summary fields in my Smartsheet project where I show the number of projects targeted to close in Q1-23 and for Q2-Q4-23. Here are the formulas I have created:

Q1-23

=COUNTIFS({Project Portfolio Tracker Target Date}, OR(@cell > "2022/12/31", @cell < "2023/04/01"))

Q2-Q4-23

=COUNTIFS({Project Portfolio Tracker Target Date}, OR(@cell > "2023/03/31", @cell < "2024/01/01"))

The issue I am having is both formula's are pulling back the same value of '5'. The number of projects are different if I filter manually. Can someone please tell me what I am doing wrong?

Thanks,

Scott

MichaelTCA

Hi @Scott B 12

First off, the COUNTIFS function is already set up for multiple ranges/criteria, so you can remove the OR() function.

=COUNTIFS({Project Portfolio Tracker Target Date}, @cell > "2022/12/31", @cell < "2023/04/01")

Second, technically you're saying, count the elements in range Project Portfolio with criteria of >12/31/2022, THEN count elements in range @cell < "2023/04/01". The multiple criteria in this function doesn't need to exist for it to work (only the first), so the last range (that isn't actually a range) is negligible for the function to work.

If you're only evaluating criteria from 1 range then use the COUNTIF function.

=COUNTIF({Project Portfolio Tracker Target Date}, OR(@cell > "2022/12/31", @cell < "2023/04/01"))

Try replacing OR with AND too if that doesn't help.

Scott B 12

Michael,

Thank you for your quick response. I am trying to count the total number of projects targeted to close between 01/01/23 and 03/31/23. The second query is from 04/01/23 to 12/31/23.

Thanks,

Scott

MichaelTCA

Hi again!

If you haven't figured out a way yet, try this out.

=COUNTIF([Column]:[Column], AND(@cell > DATE(2022, 12, 31), @cell < DATE(2023, 4, 1)))

Unless you're referencing a range outside of the sheet you're working in, use this format, [Column]:[Column].

An example of the other function:

=COUNTIF([Column]:[Column], AND(@cell > DATE(2023, 3, 31), @cell < DATE(2024, 1, 1)))