Count cells based on different criteria?

hkaur08 · Jan 17th 2017

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

**Ger Plante** · Jan 18th 2017

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
'Transaction - Original '!AD:AD = ColAD
Workforce!A1 = A1

makes your formula more readable... and possibly more efficient too.
=COUNTIFS(ColAI,"1A",ColJ,A1,ColA, "Executives",ColAD,"New Hire")
+COUNTIFS(ColAI,"1A",ColJ,A1,ColA, "Executives",ColAD,"Rehire")
+COUNTIFS(ColAI,"1A",ColJ,A1,ColA, "Executives",ColAD,"Promotion")
+COUNTIFS(ColAI,"1B",ColJ,A1,ColA, "Executives",ColAD,"New Hire")
+COUNTIFS(ColAI,"1B",ColJ,A1,ColA, "Executives",ColAD,"Rehire")
+COUNTIFS(ColAI,"1B",ColJ,A1,ColA, "Executives",ColAD, "Promotion")
+COUNTIFS(ColAI,"1C",ColJ,A1,ColA, "Executives",ColAD,"New Hire")
+COUNTIFS(ColAI,"1C",ColJ,A1,ColA, "Executives",ColAD,"Rehire")
+COUNTIFS(ColAI,"1C",ColJ,A1,ColA, "Executives",ColAD,"Promotion")

Then you could create named ranges for Countifs formulas themselves:

COUNTIFS(ColAI,"1A",ColJ,A1,ColA, "Executives",ColAD,"New Hire") = "NewHire"
COUNTIFS(ColAI,"1A",ColJ,A1,ColA, "Executives",ColAD,"Rehire") = "Rehire"
etc. etc.

So your formula then becomes
=NewHire + Rehire + etc. etc.

HTH

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

Ger

Count cells based on different criteria?

Participate now!

Share