I am looking to count the number of checked boxes in a column based on the Fiscal Year?

Options

From the below screen shot I am trying to count in the Sheet Summary the number of original stories in the fiscal year July 1, 2022 to June 30, 2023.

I found this formula in the community help, however I cannot seem to apply to my sheet, I am either receiving an #unparseable, or #invalid operation era depending on how I change the formula to ref my column titles.

This is the formula giving the Invalid operation, =COUNTIFS([Original Story]:[Original Story], "1", AND(@cell >= IF([Completion Date]:[Completion Date] >= DATE(YEAR(TODAY()), 7, 1), DATE(YEAR(TODAY()), 7, 1), DATE(YEAR(TODAY()) - 1, 7, 1)), @cell <= IF([Completion Date]:[Completion Date] >= DATE(YEAR(TODAY()), 7, 1), DATE(YEAR(TODAY()), 6, 30), DATE(YEAR(TODAY()) + 1, 6, 30))))

This is the formula-source from Community help search I started with changing the range cell to my range cell title.

=COUNTIFS({Date}, AND(@cell >= IF(TODAY() >= DATE(YEAR(TODAY()), 7, 1), DATE(YEAR(TODAY()), 7, 1), DATE(YEAR(TODAY()) - 1, 7, 1)), @cell <= IF(TODAY() >= DATE(YEAR(TODAY()), 7, 1), DATE(YEAR(TODAY()), 6, 30), DATE(YEAR(TODAY()) + 1, 6, 30))))

I full accept I maybe using the wrong formula, I just started wording with Smartsheet a few weeks ago and really want to learn all I can.

Thanks for any help

Kelly

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Hi Kelly

    The formula you are trying to adapt is more complicated than I think you need. How about this:

    =COUNTIFS([Original Story]:[Original Story], "1", [Completion Date]:[Completion Date], AND(@cell >= DATE(2022, 7, 1), @cell <= DATE(2023, 6, 30)))


    This has two criterion and is counting the number of rows where

    • [Original Story] is checked
    • and
    • [Completion Date] is on or after Jul 1, 2022 and on or before June 30, 2022
  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 11/09/23
    Options

    Hi @Kelly Dawson

    I prefer to make the form readable using Sheet Summary fields, like [fiscal year start]#.

    =COUNTIFS([Completion Date]:[Completion Date], AND(@cell >= [fiscal year start]#, @cell <= [fiscal year end]#), [Original Story]:[Original Story], true)

    If you want to use the TODAY function to work every year, the formula would be;

    =COUNTIFS([Completion Date]:[Completion Date], AND(@cell >= DATE(YEAR(TODAY()) - 1, 7, 1), @cell <= DATE(YEAR(TODAY()), 6, 30)), [Original Story]:[Original Story], true)

    You can check the formula's work by checking or unchecking the Original Story column in the above-published demo sheet.😀


  • Kelly Dawson
    Kelly Dawson ✭✭✭✭
    Options

    @KPH

    Thank you I was feeling like it was too complicated as well. I had been trying to figure out the date formulas and countifs to make it less complicated and confusing. I really appreciate the advise and help it seems to be the solution.

  • Kelly Dawson
    Kelly Dawson ✭✭✭✭
    Options

    @jmyzk_cloudsmart_jp, @KPH

    Thank you as well for this formula I am going to play around with both this solution and @KPH's solution to figure out which would be easier for my team to work with once I get the tracking sheet developed and passed off.

    I will need to see how they work as we move forward and have more tasks and fiscal years etc.

  • Kelly Dawson
    Kelly Dawson ✭✭✭✭
    Options

    @jmyzk_cloudsmart_jp

    I just copied and pasted the formula using Sheet Summary fields, like [fiscal year start]#, it returned #unparseable? The Today function formula worked

    Next thing I need to figure out would be to changes the formula to do FY24 (July 1, 2023 and June 30, 2024).

    I see how you took today's date and are subtracting 1 from the year to go back to July 1, 2022. I changed the formula removing the 1 so the start date is July 1, 2023 (current year) and added +1 to the ending year date for June 30, 2024 however the return is an incorrect Argument set, can you advise why? I included the modified formula.

    =COUNTIFS([Completion Date]:[Completion Date], AND(@cell >= DATE(YEAR(TODAY()), 7, 1), @cell <= DATE(YEAR(TODAY()), +1, 6, 30)), [Original Story]:[Original Story], true)

    I am very appreciative of your help.

    Thank you

    Kelly

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Options

    @Kelly Dawson

    As for the Sheet Summary fields, like [fiscal year start], you do not have to use a formula. You can put the date directly. A typical use-case would be to copy the sheet as the fiscal year changes and manually change the [fiscal year start] and [fiscal year end].

    That way, you do not have to mess with formulas.

    As for the #unparserable error, it is hard to guess what went wrong, but if you put July 1, 2022, in the [fiscal year start] field, [fiscal year start]# works the same way as  DATE(2022, 7, 1) in the formula.

    # is a way to use the Sheet Summary field's value in a formula.

  • Kelly Dawson
    Kelly Dawson ✭✭✭✭
    Options

    @jmyzk_cloudsmart_jp

    Thank you, after posting my comment I looked at your sample sheet some more and realized what you had done. I did like the Fiscal Year as a summary sheet field, and am using your formula.

    I am working on expanding the formula to now count the number of times my teams posts to various media channels, this is counting the number of time a date shows in the cell on sheets. Trying to use the formula you provided however I am getting a zero. I assume I need to change the Criteria after the column range from true for checked box date formula. Just not sure any advice?

    Thank you again for the all the help

    Kelly

  • Kelly Dawson
    Kelly Dawson ✭✭✭✭
    Options

    Here is Screen Shot of sheet and summary formula

    =COUNTIFS([Completion Date]:[Completion Date], AND(@cell >= [2023 Fiscal Year Start]#, @cell <= [2023 Fiscal Year End]#), [Research LinkedIn]:[Research LinkedIn], true)


  • Kelly Dawson
    Kelly Dawson ✭✭✭✭
    Options

    I had an epiphany, my solution is a simple count with the fiscal year date range, sometime we look to deep.

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Options

    @Kelly Dawson

    Yes, a simple count will do.😁

    If you want to make sure you count dates only, use this;

    =COUNTIFS([Research LinkedIn]:[Research LinkedIn], ISDATE(@cell))

  • Kelly Dawson
    Kelly Dawson ✭✭✭✭
    Options

    @jmyzk_cloudsmart_jp

    Thank you I will make a note of that for the future

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!