Countif Formula based on Column

Options

I am looking to find a formula that will count if it is a quarter column.

My sheet has columns - Fiscal Year, Quarter 2/3 (etc.) and then Monthly breakdowns.

FY1 Total | Q1 Total | Jan | Feb | Mar | Q2 Total | Apr | May ...

And continues on for a couple of FY.

I want to get a count of how many quarters have a value in it.

Our previous sheet did not need monthly columns, and had the quarters next to one another. Now we are migrating to add in monthly totals.


Is there a way to count if there is a value ONLY in the quarter column (it could span across greater than 9 quarters depending on the project)?


Thank you in advance!


Answers

  • Katy H
    Katy H ✭✭✭✭✭✭
    Options

    Yes, if you are just looking to count if it has a value in the quarter column you can just do a simple countif. I am counting values in the "Total Qtrs" column since you outlined that one, hopefully I am assuming correctly!

    =COUNTIF([Total Qtrs]:[Total Qtrs], NOT(ISBLANK(@cell)))

    If instead you want to count if it is greater than zero you would write a formula like this:

    =COUNTIF([Total Qtrs]:[Total Qtrs], >0)

    Katy Hall

    Head of Product Management

    ILLA Canna

    LinkedIn

  • A. Van Horn
    Options

    What I was hoping for, was the ability to count non-consecutive/adjacent cells.

    We have multiple fiscal year quarters on the sheet for the longer duration projects, and I am trying to get a count of how many quarters have data entered in.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You are going to need to use multiple IF statements "added" together (not nested).

    =IF([FY24 Q1]@row <> "", 1, 0) + IF([FY24 Q2]@row <> "", 1, 0) + IF([FY24 Q3]@row <> "", 1, 0) + .......................


    The above is for counting the number of cells that are not blank. If you needed to count the number of cells that are greater than zero, then you would need to change <> "" to > 0.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!