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

Kelly Dawson

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

KPH

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

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)

https://app.smartsheet.com/b/publish?EQBCT=d8f3daa566674f148ce86ac660db4988

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

Kelly Dawson

@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

@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

@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

@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

@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

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

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

jmyzk_cloudsmart_jp

@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

@jmyzk_cloudsmart_jp

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