multiple criteria for COUNTIFS

[BurkeDad]

I am trying to create a COUNTIFS formula with multiple criteria. When I create the formula for 2 criterias, it works fine, but when I add a third, it returns a value of 0 - and I know the answer should be 5. Here is the first formula - =COUNTIFS(Retirement!$A$2:$A$12085,'Cost Center View'!$E$4,Retirement!$E$2:$E$12085,0).

Here is the new formula with just one more criteria added - =COUNTIFS(Retirement!$A$2:$A$12085,'Cost Center View'!$E$4,Retirement!$E$2:$E$12085,1,Retirement!$E$2:$E$12085,2)

a2:a12085 is the Location Number, E4 is the specific location I am searching for, and Retirement E2:E12085 is the years until retirement. I am trying to count the number of empliyees that will be retiring in specific year ranges.

 

[Domenic]

Try...

=COUNTIFS(Retirement!$A$2:$A$12085,'Cost Center View'!$E$4,Retirement!$E$2:$E$12085,">=1",Retirement!$E$2:$E$12085,"<=2")

 

[BurkeDad]

What I ended up doing was putting the >1 and <2 outside of the form and refering to those cells in the formula. That way, if the numbers ever changed, I would not have to mess with the formula.

Thanks