How do i get a sum of monthly headcount using multiple criteria

hi all,

I am working on a workbook and i would like to create a formula the could help to automatically calculate the variance of the monthly headcount (budget (which i will simply use cell reference for the budget part) - actual HC in current month). So i will need to create a formula for the actual monthly HC part with multiple criteria.

The budget HC information has been input in a table already and information for current month will be consolidated using a pivot table.

Since this is a bit complicated, i have created a dummy excel as attached.

The sheet "Name list" contains the raw data that i will have to use for the "HC Report" in sheet 2.
For example, in sheet 2 B17, i would like to calculate the variance of the position with a banding of "EX" in 'name List" C:C and micro function of "COR" in 'Name List' E:E in the current month which is October.

I have typed =DATE(YEAR(TODAY()),MONTH(TODAY()),1) on top of the table as this might be needed in the formula that i would like to create.

The monthly headcount has already been calculated in "Name List" starting from column H to S.

I know this is probably a little bit long already. Now the problem is for the example of finding the October headcount with "COR" and "EX", i have created a formula of =COUNTIFS('Name List'!C:C,"EX",'Name List'!E:E,"COR") (please see E17 in Sheet 2) but i am not able to figure out how the formula can take into consideration of the HC information for the current month.

I have tried adding the part of Column H:S / Last Date (YTD) (Column G) of Name List into the COUNTIF formula, however it does not work. I have tried using LOOKUP/MATCH functions as well but seems like it won't work.

I understand this sounds complicated and I am not sure whether there would be a formula that can solve the problem at all.

Also i am not sure whether this long passage has violate any rules of the forum but i hope you experts can kindly help with this. Thank heaps in advance.

What is your desired result in sheet two B17 ????

can you not just add in extra criteria for your countif?

Formula:

Please Login or Register  to view this content.

firstly b13 does not match what you have in name list
so make it 15 to match

Formula:

Please Login or Register  to view this content.

then put into B17

Formula:

Please Login or Register  to view this content.

the numbers will update based on month in b13

In reference post #3 .

We can use dynamic range for Q:Q column

Try B17=COUNTIFS('Name List'!$C$4:$C$17,$A17,'Name List'!$E$4:$E$17,B$16,OFFSET('Name List'!$G$3,1,MATCH(MONTH('Sheet 2'!$B$13),INDEX(MONTH('Name List'!$H$3:$S$3),),0),14),">0") and drag over and down.

Formula:

Please Login or Register  to view this content.

For last criteria we need dynamic range so according to your month - last criteria range would auto manage with this formula and you don't need to change anything in your file after applying this formulea

For avoiding 14 height and making dynamic.
Try.

B17=COUNTIFS('Name List'!$C$4:$C$17,$A17,'Name List'!$E$4:$E$17,B$16,OFFSET('Name List'!$G$3,1,MATCH(MONTH('Sheet 2'!$B$13),INDEX(MONTH('Name List'!$H$3:$S$3),),0),COUNT('Name List'!$P$4:$P$1200)-1),">0")

Formula:

Please Login or Register  to view this content.

Your welcome