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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.