countifs with multiple variables from multiple columns

[HappierGnome]

I'm in a computer work center with a daily report that shows the amount of work being done company wide each day. The return file is 14 columns that is 7000+ rows deep and I receive this file every day with new data. What I need is an individual count of the number of files from each work type completed by our 44 associates. There are other associates from other programs working the same work type that my associates work.


I have an if function that pulls out just our programs work types;


=IF(Data!G2="OUR-PROGRAM",Data!E2,"Other")


Then an array function to list unique values;


{=IFERROR(INDEX(list,MATCH(0,COUNTIF($G$1:G4,list),0)),"")}


Finally, I'm using a countif to find out how many times those unique values are counted;


=COUNTIF(Data!E:E,Report!G3)


I need to modify this last countif to also check if the associates listed under DATA!D:D matches one of our 44 associates listed under REPORT!A:A. I don't want to have to run a big long {"agent1","agent2","agent3"} string because then it has to be updated through the code with all new hires as opposed to just adding them to the bottom of the REPORT!A:A column. I'm trying to get it to be as easy to modify as possible so the people who use the report and don't know excel don't have to come to me for updates each time.


Please help!

 

[Eric W]

Welcome to the forum.

Just put the list of agents in a column somewhere, say Agents!A1:A50, then put that range in the COUNTIFS like this:

=SUM(COUNTIFS(Data!E:E,Report!G3,Data!D:D,Agents!A1:A50))
and confirm with Control+Shift+Enter.

Empty cells in the range are acceptable, so you can leave room to add new agents.