COUNTIFs formula with multiple criteria

Annali

Hello

I am struggling with a formula to count total headcount by month. I have what I am looking for listed below - anything in {} is a different column from the same sheet.

What I need to count is headcount who were hired before the end of month (ie Jan 31) {hiredate} who do not have the {status} of Inactive and that do not have the following values from column {position codes} HHLAB, temp2, INTERN, SGMT. PLUS the count of those who have a {Transferdate} between the start and end of month (ie. Jan 1 to Jan 31) and who do not have the values from column {position codes} HHLAB, temp2, INTERN, SGMT and who do not have the {transfercode} HALL, DNS, DNSI.

Here are two formulas I tried, they are very complicated and not correct:

=COUNTIFS({Status}, <>"Inactive", {Position Code}, AND(@cell <> "HHOPR", @cell <> "HHLAB", @cell <> "HHIW", @cell <> "HHTEAM", @cell <> "HHFM", @cell <> "HHGenLab", @cell <> "HHIBEW", @cell <> "HHPD", @cell <> "INTERN", @cell <> "SGMT", @cell <> "Temp2"), {Hiredate}, <=DATE(2024, 1, 31)) + COUNTIFS({TransferDate}, AND(@cell >= DATE(2024, 1, 1), @cell <= DATE(2024, 1, 31))) - COUNTIFS({Term Codes}, OR(@cell = "DNS", @cell = "DNSI", @cell = "TRANSFER", @cell = "HALL"), {Position Code}, AND(@cell <> "HHOPR", @cell <> "HHLAB", @cell <> "HHIW", @cell <> "HHTEAM", @cell <> "HHFM", @cell <> "HHGenLab", @cell <> "HHIBEW", @cell <> "HHPD", @cell <> "INTERN", @cell <> "SGMT", @cell <> "Temp2"), {TransferDate}, AND(@cell >= DATE(2024, 1, 1), @cell <= DATE(2024, 1, 31)))

=COUNTIFS({Status}, <>"Inactive", {Position Code}, AND(@cell <> "HHOPR", @cell <> "HHLAB", @cell <> "HHIW", @cell <> "HHTEAM", @cell <> "HHFM", @cell <> "HHGenLab", @cell <> "HHIBEW", @cell <> "HHPD", @cell <> "INTERN", @cell <> "SGMT", @cell <> "Temp2"), {Hiredate}, <=DATE(2024, 3, 31)) + COUNTIFS({TransferDate}, AND(@cell >= DATE(2024, 3, 1), @cell <= DATE(2024, 3, 31))) - COUNTIFS({Transfer Codes}, OR(@cell = "DNS", @cell = "DNSI", @cell = "HALL"), {Position Code}, OR(@cell = "HHOPR", @cell = "HHLAB", @cell = "HHIW", @cell = "HHTEAM", @cell = "HHFM", @cell <> "HHGenLab", @cell = "HHIBEW", @cell = "HHPD", @cell = "INTERN", @cell = "SGMT", @cell = "Temp2"), {TermDate}, AND(@cell >= DATE(2024, 3, 1), @cell <= DATE(2024, 3, 31)))

Please help!

MichaelTCA

Hello @Annali

Start by breaking up the functions and make sure you get a result for each criteria before combining them into 1 function. Then you can pin point which parts of the function are causing the errors.

You should add a helper column for a numeric value or boolean variable instead of this:

AND(@cell <> "HHOPR", @cell <> "HHLAB", @cell <> "HHIW", @cell <> "HHTEAM", @cell <> "HHFM", @cell <> "HHGenLab", @cell <> "HHIBEW", @cell <> "HHPD", @cell <> "INTERN", @cell <> "SGMT", @cell <> "Temp2")

The helper column function for would be

=IF( OR(Position Code@row= "HHOPR", Position Code@row= "HHLAB", Position Code@row= "HHIW", Position Code@row= "HHTEAM", Position Code@row= "HHFM", Position Code@row= "HHGenLab", Position Code@row= "HHIBEW", Position Code@row= "HHPD", Position Code@row= "INTERN", Position Code@row= "SGMT", Position Code@row= "Temp2"),0,1)

Then reference the helper column and use 0 or 1 as the criteria, instead of the position code within your countifs function.

Try to consolidate this as much as possible if there's repetitive conditions like this. It's much easier to create complex functions.

MichaelTCA

Hello @Annali

Start by breaking up the functions and make sure you get a result for each criteria before combining them into 1 function. Then you can pin point which parts of the function are causing the errors.

You should add a helper column for a numeric value or boolean variable instead of this:

AND(@cell <> "HHOPR", @cell <> "HHLAB", @cell <> "HHIW", @cell <> "HHTEAM", @cell <> "HHFM", @cell <> "HHGenLab", @cell <> "HHIBEW", @cell <> "HHPD", @cell <> "INTERN", @cell <> "SGMT", @cell <> "Temp2")

The helper column function for would be

=IF( OR(Position Code@row= "HHOPR", Position Code@row= "HHLAB", Position Code@row= "HHIW", Position Code@row= "HHTEAM", Position Code@row= "HHFM", Position Code@row= "HHGenLab", Position Code@row= "HHIBEW", Position Code@row= "HHPD", Position Code@row= "INTERN", Position Code@row= "SGMT", Position Code@row= "Temp2"),0,1)

Then reference the helper column and use 0 or 1 as the criteria, instead of the position code within your countifs function.

Try to consolidate this as much as possible if there's repetitive conditions like this. It's much easier to create complex functions.

Annali

Thank you! That really helped. Here is my final formal

=COUNTIFS({Hiredate}, <=DATE(2024, 3, 31), {Status}, <>"Inactive", {PCH}, "1", {TCH}, "1") + COUNTIFS({TermDate}, AND(@cell >= DATE(2024, 3, 1), @cell <= DATE(2024, 3, 31)), {PCH}, "1", {TCH}, "1")

MichaelTCA

@Annali

Glad it worked!

Looks a lot easier to work with!