• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

countifs formula help

Nabeel

Member
hello every one
i am doing age analysis of my org.here is the short data in attached..there is different ranges i have putted with dept name..i want when i drag the formula the dept name in the formula will be changed automatically ..
 

Attachments

  • Book1.xlsx
    14.6 KB · Views: 0
Change headers I1:N1 to 20, 30, 40, 50, 59, 99
I2: =COUNTIFS($B:$B,$H2,$D:$D,">17",$D:$D,"<="&I$1)
Copy I2: down
J2: =COUNTIFS($B:$B,$H2,$D:$D,">"&I$1,$D:$D,"<="&J$1)
Copy J2: down and across

or see attached file:
 

Attachments

  • Book1-3.xlsx
    15.6 KB · Views: 0
Change headers I1:N1 to 20, 30, 40, 50, 59, 99
I2: =COUNTIFS($B:$B,$H2,$D:$D,">17",$D:$D,"<="&I$1)
Copy I2: down
J2: =COUNTIFS($B:$B,$H2,$D:$D,">"&I$1,$D:$D,"<="&J$1)
Copy J2: down and across

or see attached file:
thnx sir...there is compulsory to change the headers.
 
In your original file in I2:
=COUNTIFS($B:$B,$H2,$D:$D,">"&LEFT(I$1,2),$D:$D,"<"&IFERROR((FIND("&",I$1)>0)*99,(RIGHT(I$1,2)+1)))

Copy across and down

see attached file:
 

Attachments

  • Book1-4.xlsx
    15.5 KB · Views: 0
Last edited:
Back
Top