Counting Line Items (Rows) Based on Certain Criteria

David L.

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.

Nick Korna

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.

Debbie Sawyer

@David L.

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.

1. Create a Row Report called Count per Supplier (or something similar)
2. Point to your source sheet,
3. Add your Supplier, and the three helper columns to the report.
4. Click on Grouping and add Supplier
5. 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).
6. 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!

Debbie Sawyer

Hi @David L.

Try

=IFERROR(IF(YEAR([Closed Date]@row) = 2022, 1, 0),"")

David L.

https://community.smartsheet.com/discussion/comment/413961#Comment_413961

Worked Brilliantly! Thanks Debbie.

Nick Korna

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.

Debbie Sawyer

@David L.

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.

1. Create a Row Report called Count per Supplier (or something similar)
2. Point to your source sheet,
3. Add your Supplier, and the three helper columns to the report.
4. Click on Grouping and add Supplier
5. 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).
6. 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!

David L.

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!

Debbie Sawyer

Hi @David L.

Try

=IFERROR(IF(YEAR([Closed Date]@row) = 2022, 1, 0),"")

David L.

https://community.smartsheet.com/discussion/comment/413961#Comment_413961

Worked Brilliantly! Thanks Debbie.