Forum Discussion

hurshie's avatar
hurshie
Copper Contributor
Jun 04, 2020

Jos_Woolley 

 

Hello Jos,

 

I am trying to calculate how many individual hourly employees are in each location. I know that there are 3 just by looking at the example, but dont know how to write the formula.

 

1033 has X amount of unique hourly employees

1012 has X amount of unique hourly employees

Jos_Woolley's avatar
Jos_Woolley
Iron Contributor
Jun 04, 2020

hurshie 

 

Thanks. I think you mean:

 

=SUM(IF(FREQUENCY(IF(C$2:C$11=1033,IF(B$2:B$11="Hourly",MATCH(A$2:A$11&"",A$2:A$11&"",0))),ROW(A$2:A$11)-MIN(ROW(A$2:A$11))+1),1))

 

Change the hard-coded values (1033 and "Hourly") in the above to actual cell references for flexibility.

 

And remember that the above is an array formula.

 

Regards

  • Jos_Woolley's avatar
    Jos_Woolley
    Iron Contributor
    Jun 04, 2020

    IngeborgHawighorst 

     

    Perhaps the OP requires that the file in question be workable in the desktop version of Excel. I have Office 365, though functions such as UNIQUE, FILTER, etc. have not yet been rolled out as part of my subscription; as such, I have to use (and remain in) Excel Online when I want to employ those functions.

     

    Regards

  • IngeborgHawighorst's avatar
    IngeborgHawighorst
    MVP
    Jun 04, 2020

    hurshie You tagged the question with "Office 365". What did you mean with that? If you have Office 365, you should have the UNIQUE() function. If you don't have Office 365, why did you use that tag???

     

    Then again, why did you tag with "Admin" and "BI &  Data Analysis", "Developer" and "Excel for web". Why?

     

    Tags have a purpose. They're meant to identify what the problem is about. They're not collectables.

Resources