0

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?

My Sheet Capture

1 Answer 1

1

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).

6
  • @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, 2018 at 14:22
  • @MBur2000 updated the formula to match how your hours are formatted.
    – Excellll
    Dec 14, 2018 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, 2018 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, 2018 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, 2018 at 16:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .