Count based on two criteria from different columns

[djemy1975]

Hi colleagues,

Please help me count the global number of presence of each person depending on:
1-The name of person in column B
2- The status ("Present") in column F
https://www.mrexcel.com/board/attachments/capture-decran-2021-03-18-145239-png.34727/
https://www.mrexcel.com/board/attachments/1616075620360-png.34728/

Taking into consideration that some names are repeated .The result would be in the Report sheet "N° of participants"

Herewith a sample of my data

Best regards,

[SamT]

Create a Unique list of Names, then use CountIfs Name and "Present"

[djemy1975]

How I could not get the idea?

[SamT]

You've never "Been There and Done That." :devil2:

[djemy1975]

Never:(

Could it be something like :

=Sumproduct((Names<>"")/countif(Names;Names&""))

this to extract the number of unique names what about counting those who are "Present" in column F?

[djemy1975]

Finally ,I figured it out

=SUMPRODUCT(((Status="Present"))/COUNTIFS(Names;Names &"";Names;Names&""))

Thank you so much for the hint

[SamT]

Taught me something. I woulda just Filtered the Names to a new Unique list and use a simple CountIfs against the original table, so that I could easily add a Count Name and a CountIfs "Absent" formula to the next columns

[djemy1975]

Hi,
could you help me count the number of persons "Present" for each department? taking into account that names and departments are non unique values that should be unique before counting the number of presence.
For example for Maintenance ,I should get 3 persons present

Thanks in advance,

[SamT]

You just need a Unique list of Departments

Against that List you can CountIfs(Department(By Range), Names, Status, Functions, etc.)

Some Unique lists are very short, ie: Unique Status only has two entries at this time ; You might add Vacation, Medical, PTO, in the future.
Some Unique lists are very long: ie. Code+Name and maybe, but I dissuggest, Department and Function.

The only Unique List requirement is that all items in the List are 1 to 1. ie; 1 Code per Name, 1 Name per Code. If you ever need to count male vs female, you will need Unique Genre list.

Each Department is associated with many Names, therefore the Unique Department list must be a single column