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

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

• Hi Kelly

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

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

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

• 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

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

• 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

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

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

• Yes, a simple count will do.😁

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