Using a data validation drop-down list with countifs formulas

[ngelbke]

I have a table created that uses countifs formulas to pull data. I want the data to change based by client - so I created a drop down list with each client's name, and thought I could add a criteria to the countifs formula based on the client's name in the cell- but I get a value error. Any suggestions?

 

[J_HRDA]

Hello, I have the exact same issue however I have multiple (6) criteria within the COUNTIFS function. Any ideas on how to I specify the "display all" element?


Particularly 2 conditions relating to dates and 4 which relate to factors with many levels (between 2-6). I am using excel 2010.

Purpose is to measure employee headcount on certain dates from a dataset that the sample looks like below (normally has c.18K records):

ID	Start Date	Left Date	Department	Fee earning flag	Area	Employee Type
1	01/01/2015		Systems	Fee earning	Asia	EFT
2	14/05/2017	02/03/2018	Services	Non-fee earning	Europe	ER
3	02/02/2018		Systems	Fee earning	America	CFT
4	21/04/2013	19/05/2018	Services	Non-fee earning	UK	CON

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Formula structure:
=COUNTIFS( Start date < Date, End date >=Date, Department = ..., Employee Type = ..., Fee earning flag = ..., Area = ...) + COUNTIFS( all same but End date = blank, this is to add an OR functionality for left date)

Logic description example:
Count every employee with a start date before i.e.01/07/2015 (so person has started by that date) AND [left date EITHER after 01/07/2015 (leaver after that date) OR blank] AND conditions about department, area etc..

Formula
=COUNTIFS(Base_DATA!$H$2:$H$18454,"<" & B3,Base_DATA!$J$2:$J$18454,">=" & B3,Base_DATA!$AE$2:$AE$18454,$V$2,Base_DATA!$BK$2:$BK$18454,$V$3,Base_DATA!$U$2:$U$18454,$V$4,Base_DATA!AL2:AL18454,$V$5)+COUNTIFS(Base_DATA!$H$2:$H$18454,"<" & B3,Base_DATA!$J$2:$J$18454,"",Base_DATA!$AE$2:$AE$18454,$V$2,Base_DATA!$BK$2:$BK$18454,$V$3,Base_DATA!$U$2:$U$18454,$V$4,Base_DATA!AL2:AL18454,$V$5)

B3 includes the date I want to calculate the headcount and V2,3,4,5 the dropdown lists for each of the factors explained before.

I would greatly appreciate your help.
Please advice if it is better to open a new thread , I just felt that it is the extension of the issue discussed here.

 

[Novice79]

Maybe this...

=IF(A1="All",COUNTIF('All Lates - Data'!F2:F491,'Late Workorder Breakdown'!A9,COUNTIFS('All Lates - Data'!F2:F491,'Late Workorder Breakdown'!A9,'All Lates - Data'!$C$2:$C$491,A1))

Hi T.Valko - you have been a lifesaver for me with this response. The only query I have is how would you change this formula to count everything (based on other criteria) if A1 cell is empty, to give overarching numbers? I have created a dashboard that needs to be able to flex based on the data validation list but also provide an overall summary. Thanks