Counting Line Items (Rows) Based on Certain Criteria
Hello, this is my 1st posted request for assistance and hoping someone could assist. Above is an example of my Sheet, Columns, and mock-up data for use as an example. Within this Sheet I have (3) helper columns I've created and looking for formula help for each to capture a "1" in [Closed in 2022? Helper Column] if [Date Closed] in row indicates year (2022) and if not, leave blank or capture "0". Then similar formula which I could replicate in helper columns for those rows closed in 2023 and 2024 respectively by changing the year in each to match.
Once I had the helper columns capturing this I would greatly appreciate formula assistance I place to add to my Calculations Sheet to capture the following for each Supplier & Year for example:
- [Supplier-A]
- (2022) Count of line items Opened
- (2022) Count of line items Closed
Any help you could provide would be greatly appreciated and if there's an easier way to capture this, like in Sheet Summary or Automated Workflow I'd love to hear it. Thanks again and I love this forum and the help provided seen thus far. I've been able to use it productively by reverse engineering similar recommendations provided to similar questions when found, but this had been giving me issues to do the same thus far.
Best Answers
-
Hi @David L.
Your helper columns would be along the lines of:
=IF(YEAR([Closed Date]@row) = 2022, 1, 0)
Obviously changing 2022 for the other years as required.
For your calculations sheet (I'm assuming you have some familiarity with how to do cross sheet references):
For opened in 2022:
=COUNTIFS({Supplier}, "Supplier A",{Date Opened}, YEAR(@cell)=2022)
Similarly for closed in 2022:
=COUNTIFS({Supplier}, "Supplier A",{Current State},"Closed",{Closed Date}, YEAR(@cell)=2022)
You can skip needing the helper columns using this method if that is the reason for creating them.
If you had your suppliers listed in the first column (named Supplier) of your cross sheet and the columns set to Opened in 2022, Closed in 2022, Opened in 2023, etc. then you could amend the formulas along the lines of:
=COUNTIFS({Supplier}, Supplier@row ,{Date Opened}, YEAR(@cell)=2022)
Hope this helps/gives you some ideas, but if I've misunderstood anything or you've any problems/questions then just let us know.
-
Welcome to the community!
You have a great answer here from @Nick Korna
The formula given for the helper columns are good, as are the formula for the stats.
Alternative method for stats:
I just wanted to add, an easier way of producing the stats, without the need for a metric sheet, is to just run a report with grouping and summaries.
- Create a Row Report called Count per Supplier (or something similar)
- Point to your source sheet,
- Add your Supplier, and the three helper columns to the report.
- Click on Grouping and add Supplier
- Click on Summaries and select Closed in 2022 helper column with the function set to Count (or sum, both will work as you are only using 1 and 0).
- Add two more summaries for the other two helper columns
When you run the report in a collapsed state, you will see the totals for each supplier.
No formulas or functions required.
Just a different method which doesn't require as many functions :D
Enjoy!
-
-
Worked Brilliantly! Thanks Debbie.
Answers
-
Hi @David L.
Your helper columns would be along the lines of:
=IF(YEAR([Closed Date]@row) = 2022, 1, 0)
Obviously changing 2022 for the other years as required.
For your calculations sheet (I'm assuming you have some familiarity with how to do cross sheet references):
For opened in 2022:
=COUNTIFS({Supplier}, "Supplier A",{Date Opened}, YEAR(@cell)=2022)
Similarly for closed in 2022:
=COUNTIFS({Supplier}, "Supplier A",{Current State},"Closed",{Closed Date}, YEAR(@cell)=2022)
You can skip needing the helper columns using this method if that is the reason for creating them.
If you had your suppliers listed in the first column (named Supplier) of your cross sheet and the columns set to Opened in 2022, Closed in 2022, Opened in 2023, etc. then you could amend the formulas along the lines of:
=COUNTIFS({Supplier}, Supplier@row ,{Date Opened}, YEAR(@cell)=2022)
Hope this helps/gives you some ideas, but if I've misunderstood anything or you've any problems/questions then just let us know.
-
Welcome to the community!
You have a great answer here from @Nick Korna
The formula given for the helper columns are good, as are the formula for the stats.
Alternative method for stats:
I just wanted to add, an easier way of producing the stats, without the need for a metric sheet, is to just run a report with grouping and summaries.
- Create a Row Report called Count per Supplier (or something similar)
- Point to your source sheet,
- Add your Supplier, and the three helper columns to the report.
- Click on Grouping and add Supplier
- Click on Summaries and select Closed in 2022 helper column with the function set to Count (or sum, both will work as you are only using 1 and 0).
- Add two more summaries for the other two helper columns
When you run the report in a collapsed state, you will see the totals for each supplier.
No formulas or functions required.
Just a different method which doesn't require as many functions :D
Enjoy!
-
Thank you Nick and Debbie!!! Both worked perfectly, but I could I request additional assistance from what resulted? The formula: =IF(YEAR([Closed Date]@row) = 2022, 1, 0) accomplished what I had requested. However, it leaves "#INVALID DATA TYPE" within [Helper Column] cells which aren't applicable. What can I add to this formula to leave those cells blank? Thanks in advance for any rapid response!
-
-
Worked Brilliantly! Thanks Debbie.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
