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):

[TABLE="width: 567"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Start Date[/TD]
[TD]Left Date[/TD]
[TD]Department[/TD]
[TD]Fee earning flag[/TD]
[TD]Area[/TD]
[TD]Employee Type[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]01/01/2015[/TD]
[TD][/TD]
[TD]Systems[/TD]
[TD]Fee earning[/TD]
[TD]Asia[/TD]
[TD]EFT[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]14/05/2017[/TD]
[TD] 02/03/2018[/TD]
[TD]Services[/TD]
[TD]Non-fee earning[/TD]
[TD]Europe[/TD]
[TD]ER[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]02/02/2018[/TD]
[TD][/TD]
[TD]Systems[/TD]
[TD]Fee earning[/TD]
[TD]America[/TD]
[TD]CFT[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]21/04/2013[/TD]
[TD] 19/05/2018[/TD]
[TD]Services[/TD]
[TD]Non-fee earning[/TD]
[TD]UK[/TD]
[TD]CON
[/TD]
[/TR]
</tbody>[/TABLE]

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