Count formula help
I need to count number of tickets in a few columns, independently, and present the calculations in a metrics sheet to be used later. I need to calculate weekly and monthly numbers. I have tried many iterations of formulas and continue to get errors. Would love your thoughts.
=COUNTIF({Week #}:[Week #], ${Week #}:$[Week #]@row)
Metrics used for monthly calculations:
Sheet {BDA defect metrics} with mocked data
Week Ending | Total JIRA tickets created | formula test | Week # | Month | Column3 | Column4 | Column5 | Column6 | Column7 | Column8 | Column9 |
---|---|---|---|---|---|---|---|---|---|---|---|
Week ending | Total JIRA Tickets Created | Still not working I have a question out to my guru | Week # | Month | Volume of Tickets w/Peer Review Defects | Volume of Defects Initial Peer Review | Volume Repeat Defects from Peer Review | Total Defects Peer Review | Initial Peer Review Quality % (Defects) | Overall Peer Review Quality % (Defects) | Internal Defective Rate (Tickets) |
01/05/24 | 1 | January | |||||||||
01/12/24 | 2 | January | |||||||||
01/19/24 | 3 | January | |||||||||
01/26/24 | 4 | January | |||||||||
02/02/24 | 84 | =COUNTIF({BDA Peer Review Defect Tracker Range 1}:{BDA Peer Review Defect Tracker Range 1}, @cell) | 5 | February | 5 | 5 | 2 | ||||
02/09/24 | 85 | 6 | February | 4 | 4 | 1 | |||||
02/16/24 | 77 | =COUNT({Week Number}:{Week Number}, Week #]:[Week #], @row) | 7 | February | 9 | 9 | 0 | ||||
02/23/24 | 89 | 8 | February | 6 | 6 | 0 | |||||
03/01/24 | 102 | 9 | March | 2 | 2 | 0 | |||||
03/08/24 | 84 | 10 | March | 1 | 1 | 0 | |||||
Landing for calculation, as you can see, I will also need to figure out some percentages.
Month | Volume of Tickets w/Peer Review Defects | Volume of Defects Initial Peer Review | Volume Repeat Defects from Peer Review | Total Defects Peer Review | Initial Peer Review Quality % (Defects) | Overall Peer Review Quality % (Defects) | Internal Defective Rate (Tickets) |
---|---|---|---|---|---|---|---|
January | |||||||
February | |||||||
March | |||||||
Sheet {BDA Peer Review Defect Tracker}
Data for all metrics:
Month | Week # | Date of QC |
---|---|---|
April | 14 | 04/01/24 |
April | 14 | 04/01/24 |
April | 14 | 04/01/24 |
April | 14 | 04/02/24 |
April | 14 | 04/03/24 |
April | 14 | 04/03/24 |
April | 14 | 04/03/24 |
I appreciate your time and advice!
Thank you,
Answers
-
Hi Michele… the issue is the construction of your ranges in your formula
=COUNTIF({Week #}:[Week #], ${Week #}:$[Week #]@row)
You use {} when referencing another sheet. To do that, instead of typing it out, click the "Reference another sheet" link in the formula help popup box that appears when you start typing "= COUNTIF ( ". You have to get to the ( part of the formula typing before the box appears.
When the box appears, find the sheet that you want to reference and click the column header to select the entire column, give it a name, and hit OK. You'll see that your formula then should look something like
= COUNTIF( {Week #}
You do not need to put the : and "ending part" of the range into your formula. The cross-sheet reference for {Week #} includes both the start and end of the range.
Type , to move to the second argument, the criteria.
When you're referencing the Week # cell on the row, it's typed out like this [Week #]@row
Don't put a "start and end" reference, and don't use the $.
When done your working formula should look like this:
= COUNTIF ( {Week #}, [Week #]@row )
Don't forget to then right click the formula and choose "Convert to Column Formula" to apply it to all rows.
-
Thank you Brian! I will try this and let you know.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!