CountIf Problems

**kennedyJason** · 07-12-2011, 09:27 AM

So this is a little strange and I am having difficulty figuring out how to calculate these numbers. I have multiple sheets with active dates on them under this code:

=(IF(J18="",0,DATEDIF(J18,TODAY(),"md")&" days , "&DATEDIF(J18,TODAY(),"ym")&" months, "&DATEDIF(J18,TODAY(),"y")&" years

This gives me a current break down of months days and years a project is running in a single cell.

Then on a different sheet I want a running total of the projects that are 0-2 months old, 3-5 months old, 6-8 months old, 9-11 months old, and then a year and older. So I did this formula for counting one of the months.

=+SUM(COUNTIF(January!N17:N4010,"*6 months*") which works great, the problem is if something is 1 year and 6 months old or 2 years and 6 months old it is also counted in this number. Is there a way I can just calculate the ones that are 6 months and not those that are 1 year and 6 months etc.

Thanks
Jason

**NBVC** · 07-12-2011, 09:38 AM

Perhaps:

=COUNTIF(January!N17:N4010,"*6 months*0 years")

Note: the +SUM() is redundant and not necessary.

**Harley2** · 07-12-2011, 09:42 AM

NBVC.

Just a quick question, wouldn't that also pick-up 10 years, 20 years,.....
Maybe:
=COUNTIF(January!N17:N4010,"*6 months, 0 years")
would work?

**NBVC** · 07-12-2011, 09:53 AM

Good point Harley2. You are correct... although I am not sure how the OP will get all other combinations...

probably better to have another column in the main data that actual calculated the difference in dates and then use countif to determine greater than and/or less than certain number of days.... (i.e. for 6 months... 180 days)