# Count cells based on different criteria?

• 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.

Code
``````=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")``````
• Re: Count cells based on different criteria?

First of all, make your formula easier to read... Create named ranges for the repetitively used references...

'Transaction - Original '!AI:AI = ColAI
'Transaction - Original '!J:J = ColJ
'Transaction - Original '!A:A = ColA
Workforce!A1 = A1

Then you could create named ranges for Countifs formulas themselves:

etc. etc.

=NewHire + Rehire + etc. etc.

HTH

NB, I moved your thread for "Tips and Tricks" into "Excel formulas"

Ger

_______________________________________________
There are 10 types of people in the world. Those that understand Binary and those that dont.

Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

_______________________________________________

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!