Countif Formula based on Column
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
-
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)
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!