I have an excel work sheet that has columns each column has names and numbers.
column AI has job categories (1A,1B,1C......)
Column A has the categories ( Executives , Directors,....)
Column AD has the transtaction ( New Hire, Rehire, Promotions)
Column J has the names of the leaders ( dan , steven, sally....) Workforce!A1 has the name of the person we are counting for.
In worksheet 2 I have to count how many Executives from job cat 1A,1B,1C where New hires, rehires, promotions under Dan
I am using Countifs to calculate the sum and its working fine but the formula keeps getting bigger as I have to and race in another calculation. My question is that is there an easies way to do this without having to write this long formula everytime I have to look for something specific.
=COUNTIFS('Transaction - Original '!AI:AI,"1A",'Transaction - Original '!J:J,Workforce!A1,'Transaction - Original '!A:A, "Executives",'Transaction - Original '!AD:AD,"New Hire")
+COUNTIFS('Transaction - Original '!AI:AI,"1A",'Transaction - Original '!J:J,Workforce!A1,'Transaction - Original '!A:A, "Executives",'Transaction - Original '!AD:AD,"Rehire")
+COUNTIFS('Transaction - Original '!AI:AI,"1A",'Transaction - Original '!J:J,Workforce!A1,'Transaction - Original '!A:A, "Executives",'Transaction - Original '!AD:AD,"Promotion")
+COUNTIFS('Transaction - Original '!AI:AI,"1B",'Transaction - Original '!J:J,Workforce!A1,'Transaction - Original '!A:A, "Executives",'Transaction - Original '!AD:AD,"New Hire")
+COUNTIFS('Transaction - Original '!AI:AI,"1B",'Transaction - Original '!J:J,Workforce!A1,'Transaction - Original '!A:A, "Executives",'Transaction - Original '!AD:AD,"Rehire")
+COUNTIFS('Transaction - Original '!AI:AI,"1B",'Transaction - Original '!J:J,Workforce!A1,'Transaction - Original '!A:A, "Executives",'Transaction - Original '!AD:AD, "Promotion")
+ COUNTIFS('Transaction - Original '!AI:AI,"1C",'Transaction - Original '!J:J,Workforce!A1,'Transaction - Original '!A:A, "Executives",'Transaction - Original '!AD:AD,"New Hire")
+COUNTIFS('Transaction - Original '!AI:AI,"1C",'Transaction - Original '!J:J,Workforce!A1,'Transaction - Original '!A:A, "Executives",'Transaction - Original '!AD:AD,"Rehire")
+COUNTIFS('Transaction - Original '!AI:AI,"1C",'Transaction - Original '!J:J,Workforce!A1,'Transaction - Original '!A:A, "Executives",'Transaction - Original '!AD:AD,"Promotion")