# Should I use, COUNTIF, Array or something else in my Excel Sheet?

I have a spreadsheet from which I want to pull certain "count" information.

In column R I have a list of hours and in column T I have a list of names (an array created from all the names in the sheet:
`{=IFERROR(INDEX(EngList, MATCH(0,COUNTIF(\$T\$1:T1, EngList), 0)),"")}`.

I want to COUNT:

• how many times each name in the array appears in the sheet
• how many times each name in the array has an entry in column R of less than 120

I have tried using COUNTIF and COUNTIFS, but cannot seem to get anything other than 0 as a result. Any ideas? To get the number of times a name in T2 appears, you can use `COUNTIF`.

``````=COUNTIF(\$B\$2:\$B\$51,T2)
``````

Here, the column of names in your table is in B2:B51. Adjust this to match your data. You should be able to fill this formula down column U in your sheet.

To get the count of times the name in T2 had hours less than `120:00`, you can use `COUNTIFS`.

``````=COUNTIFS(\$R\$2:\$R\$51,"<5",\$B\$2:\$B\$51,T2)
``````

Here, R2:R51 is your column of hours and B2:B51 is the column of names in your table. You should also be able to fill this formula down column V in your sheet.

Note that the comparison value is 5, which is the internal numeric representation that Excel uses to store the datetime `120:00` (basically, this means 5 days).

• @MBur2000 I just noticed the hours data in your sheet is formatted as a time. This will require a change in the formula. – Excellll Dec 14 '18 at 14:22
• @MBur2000 updated the formula to match how your hours are formatted. – Excellll Dec 14 '18 at 14:36
• Great Thank You, that works in the example. If My table grows, could I use R:R, B:B etc in the formula instead of the range \$R\$2:\$R\$51? What about a named range perhaps? – MBur2000 Dec 14 '18 at 16:00
• Yes, that's just how I set it up in my example. Using a column reference shouldn't be a problem. – Excellll Dec 14 '18 at 16:15
• Actually I just tried it with the R:R etc and it works fine in the larger table. Thanks for your help – MBur2000 Dec 14 '18 at 16:19