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

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni

    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.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • Thank you Brian! I will try this and let you know.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!