COUNTIF based on a Sheet Summary Cell w/Formula?

Jen Castillo

Hello!

I am trying to create rolling metrics of how many entries we have in the current Fiscal Year, Quarter, and past Fiscal Year. How would I reference a Sheet Summary cell's value?

For ex, I want Total Orders of Fiscal Year to show me everything in FY23. My Total Orders of Fiscal Year cell has the formula-

=COUNTIF(FY:FY, [Current Fiscal Year]#)

However, my COUNTIF is returning #INVALID DATA TYPE.

Here are the formulas in each of these sheet summary cells.

Date

=TODAY(0)

Current Fiscal Year

=IF(MONTH(Date#) > 9, YEAR(Date#) + 1, YEAR(Date#))Current Quarter

Current Quarter

=IF(MONTH(Date#) <= 3, "Q2", IF(MONTH(Date#) <= 6, "Q3", IF(MONTH(Date#) <= 9, "Q4", "Q1")))

Previous Fiscal Year

=[Current Fiscal Year]# - 1

I have the variations of the same formula in my grid to "tag" each entry with QTR/FY, so the information matches. I am able to do a COUNTIF if I specify "2023" or "2024," but since I want it to be rolling, I do not want to have to update the value each time.

I am also open to other workarounds if there is an easier way to do this.

Thank you!

Leibel S

@Jen Castillo

check if any value in the FY column has the error #INVALID DATA TYPE

Leibel S

@Jen Castillo

check if any value in the FY column has the error #INVALID DATA TYPE

Jen Castillo

@Leibel S Hello!

My dates columns do have #INVALID DATA TYPE because they are basing off dates of only completed orders. How would I format with MONTH/YEAR formulas to ignore blanks? IFERROR or ISBLANK?

=IF(MONTH([Delivered Date]@row) > 9, YEAR([Delivered Date]@row) + 1, YEAR([Delivered Date]@row))

I tried a bunch of things but syntax is not my strong point!

=IFERROR(OR(MONTH([Delivered Date]@row) > 9, YEAR([Delivered Date]@row) + 1, YEAR([Delivered Date]@row))"")

Actually I got it working!! I added in ISDATE! Thanks for your help, Leibel. :)

=IF(ISDATE([Delivered Date]@row), IF(MONTH([Delivered Date]@row) > 9, YEAR([Delivered Date]@row) + 1, YEAR([Delivered Date]@row)), "")

Leibel S

@Jen Castillo

Glad you figured it out!