Calculating number of personnel by status and hire date

[F6Hawk]

I am trying to create a formula that will compare data in two columns on a sheet and return the count of those employees.


The goal is to return the number of managers hired in 2003:
=SUMPRODUCT(IF(EmpList!$G$2:$G$15000,"Mgr")*(YEAR((EmpList!$H$2:$H$15000)=2003)))

Column G holds their positions (Mgr, Emp1, Emp2, etc.), and column H is the hire date in mm/dd/yy format.

But I am returning #value.

What would be the correct formula?

TIA

[alansidman]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[F6Hawk]

Here ya go.

[alansidman]

instead of a formula, I would employ Power Query. Here is the Mcode for that

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hire Date", type date}, {"Birthdate", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Hire Date]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Year", {"Year", "Stat"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

If you only want to do lookups for particular years, then post back and I will modify the query to that means.

[F6Hawk]

Thanks Alan. I am currently using 2016 for Mac (Retired), but have purchased and need to install Office 365; is it included in either of these versions?

I'm not finding it for Excel for Mac...

[F6Hawk]

So without this Power Query option... what formula would return my needed data?

Thanks!

[alansidman]

Power Query is included in O365 and is called Get and Transform Data and found on the Data Tab. It is not on Excel for Mac.

[CARACALLA]

Counts


A2=IF(Counts!A$1<>"",SUMPRODUCT((EmpList!$F$2:$F$1000=Counts!A$1)*(YEAR(EmpList!$G$2:$G$1000)=2003)),"")

Copy across

[F6Hawk]

Nailed it!! Thanks so much @CARACALLA! So simple, now that I see your solution. Much appreciation to you both.

CountsA2=IF(Counts!A$1<>"",SUMPRODUCT((EmpList!$F$2:$F$1000=Counts!A$1)*(YEAR(EmpList!$G$2:$G$1000)=2003)),"")

Copy across

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.