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

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:


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


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




  • MichaelTCA
    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.

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



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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!