Count formula help

Michele Rosenberg

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,

Brian_Richardson

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.