Countifs formula question
Hello,
I am trying to pull the data from a column in one sheet to another sheet and count how many "lates" there are. The column I'm working with has the formula and covers 66 rows:
=IF([Date Opened]@row + 30 < [Date Closed]@row, "Late", "")
Is there a way to have the "lates" counted and that new data shown in my new sheet? It feels like I may be missing the counting step, but as I'm a week or so into SS usage I'm not quite sure of the possibilities.
I will also be pulling data for +60 and +90, but I'm guessing that would be laid out the same way as the +30.
Thoughts?
Ryon
Best Answer
-
Hello @Ryon,
If you are attempting to count the number of instances of "Late" in your sheet from another sheet, please consider using the COUNTIF() formula.
An example of this in use would be to use this formula in your new sheet and reference the old sheet:
You will need to type this into the Cell of the new sheet you want the number to appear in.
=COUNTIF(
Next, while the cursor is hovering after the parenthesis, a box will appear explaining the COUNTIF() function. You will want to click the "Reference Another Sheet" option. A dialog box will appear where you should navigate to the original sheet with the "Late" column. Select the column name and it should highlight the entire column for you. Before you do anything else please name the reference to something understandable, I typically name it "Column Name (Sheet acronym)" So if the column name is "Late" and the sheet name is "Daily Capacity Rating" I would name it "Late (DCR)". This is the best practice to get into right now while you are starting out so that when you return to your formulas if they stop working, you can immediately understand what your references refer to.
So if you do all that you should get something like this:
=COUNTIF({Late (DCR)}, ="Late")
Hopefully this solution works for you. If not, you can reply and I can take a closer look.
If you need more than one condition, you can use the COUNTIFS() function.
Hope this helps! Happy Learning!
Answers
-
Hello @Ryon,
If you are attempting to count the number of instances of "Late" in your sheet from another sheet, please consider using the COUNTIF() formula.
An example of this in use would be to use this formula in your new sheet and reference the old sheet:
You will need to type this into the Cell of the new sheet you want the number to appear in.
=COUNTIF(
Next, while the cursor is hovering after the parenthesis, a box will appear explaining the COUNTIF() function. You will want to click the "Reference Another Sheet" option. A dialog box will appear where you should navigate to the original sheet with the "Late" column. Select the column name and it should highlight the entire column for you. Before you do anything else please name the reference to something understandable, I typically name it "Column Name (Sheet acronym)" So if the column name is "Late" and the sheet name is "Daily Capacity Rating" I would name it "Late (DCR)". This is the best practice to get into right now while you are starting out so that when you return to your formulas if they stop working, you can immediately understand what your references refer to.
So if you do all that you should get something like this:
=COUNTIF({Late (DCR)}, ="Late")
Hopefully this solution works for you. If not, you can reply and I can take a closer look.
If you need more than one condition, you can use the COUNTIFS() function.
Hope this helps! Happy Learning!
-
Thank you Christian, that helped get me from Invalid Ref to Unparseable. I'm thinking I probably missed something in this formula but it feels right...thoughts? A missed space or something?
=COUNTIF({Over 30 Days?(LIR)}, ="late")
Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!