# Excel countif problem involving age formula. urgent help needed.

I am creating a spread sheet to help me with my childminding accounts and I need to count the amount of children that I look after in one day that are over 8 years old. the table is set up as follows:

Date of birth     Age
17/02/1986     17.00
24/01/2003     0.02
12/05/1997     5.00
29/09/1999     3.00
20/02/2002     1.00
30/12/1994     8.00
02/11/2000     2.00

I need to count the amount of children that are over 8 years old. if I try to work out something that is less that a value eg. <8 then the correct answer appears. but when it is greater than 8 or >8
the wrong amswer comes up. Although I have a formula that works out age in the age column and I know that this has something to do with it because when I deleat the formula from the blank cells then the correct answer appears. unfortunatuly I do need these extra rows because the amount of children that I look after veries from week to week. the age formula is as follows:

=IF(E161="","",IF(((YEAR(TODAY())-YEAR(E161))*12+MONTH(TODAY())-MONTH(E161))<=12,((YEAR(TODAY())-YEAR(E161))*12+MONTH(TODAY())-MONTH(E161))/100,IF(MONTH(TODAY())>MONTH(E161),YEAR(TODAY())-YEAR(E161),IF(AND(MONTH(TODAY())=MONTH(E161),DAY(TODAY())>=DAY(E161)),YEAR(TODAY())-YEAR(E161),(YEAR(TODAY())-YEAR(E161))-1))))

cell E161 reffures to the date of birth of the person. this is a complicted formula so heres a breakdown of the formula:

7     =IF(E41="","",

if the cell (E41) has nothing in it, then display nothing

7     IF(((YEAR(TODAY())-YEAR(E41))*12+MONTH(TODAY())-MONTH(E41))<=12,((YEAR(TODAY())-YEAR(E41))*12+MONTH(TODAY())-MONTH(E41))/100

If (the year of today - the year in cell (E41) multiplied by 12 + the month of today  month in cell (E41) the months old is less that 12 then do the same calculation and divide by 100 to produce a decimal answer to represent months old if younger than 1 year.

7     IF(MONTH(TODAY())>MONTH(E41),YEAR(TODAY())-YEAR(E41)

If the month of today is greater that the month in cell (E41) then take the year of today from the year in cell (E41)

7     IF(AND(MONTH(TODAY())=MONTH(E41),DAY(TODAY())>=DAY(E41)),YEAR(TODAY())-YEAR(E41)

If the month of today = the month in cell (E41) and the day today = the day in cell (E41) then take the year of today from the year in cell (E41)

7     (YEAR(TODAY())-YEAR(E41))-1))))

Otherwise do the year of today  the year in cell (E41)  1

Thanks

Scotton

###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
>>> if I try to work out something that is less that a value eg. <8 then the correct answer appears
What do you use to count?
say age is in F2:F100
=COUNTIF(F2:F100,">8")

The above assumes your age formula works fine. Does your age formula returns some wrong values?

Regards,
Sebastien
0
Author Commented:
Date of birth     Age   Hours Stayed
17/02/1986     17.00         3.00
24/01/2003     0.02
12/05/1997     5.00
29/09/1999     3.00
20/02/2002     1.00
30/12/1994     8.00
02/11/2000     2.00

I fogot to mention this but I have an hours stayed column and only when in this column the value is greater that 0 i want to count the amount of children. so in the abouve example I want it to count the amount of children that have stayed more than 0 hours and that are greater than 8 years old

Scotton
0
Commented:
ok.. so in that case, you can use Array Formula.
The important thing about arraay fromula is that, after editing the formula, to validate it you have to press CTRL+SHIFT+ENTER instead of the regular ENTER.
Now, say Age is in column F2:F10 and Hours in column G2:G10 :
= SUM( (F2:F10>8) * (G2:G10>0) * 1)
--> age greater than 8, hours greater than 0

Just don't forget to press CTRL+SHIFT+ENTER instead of the regular ENTER.

Sebastien
0
Author Commented:
Thank tou sebastienm this formula works to an exstent but unfortunatly it also counts the blank cells as well. I use a formula to work out hours stayed when given the hours arrive and departed. this is the formula:

=IF(\$W42="","",IF(G42="",0,(H42-G42+(H42<G42))*24))

Again I have used the black cell trick where if a particular cell is blank then leave the cell blank. this may be the source of the problem. but how do I get rid of this problem. \$w42 is an irrelevent cell as far as this problem is concerned. you could just use letters to represent a value. g42 is the hours arrive and h42 is the hours departed. As far as I can see it appears the these formulas that I have used count a blank cell as a value greater that 0. when in fact it should be nothing. If the hours stayed formula is deleated and thn the cell is left blank, then it works. Although if it just says 0 it works. but I want the blank cells there so that it looks more presentable because I am also designing this system for a friend of mine who is crap when it comes to computers.

You must be pissed off with this by now but if you find that this problem is impossible to solve then please reply.

Scotton
0
Commented:
>>> You must be pissed off with this by now
No, not a problem :-)

You can use the same idea to remove the blanks:
= SUM( (F2:F10>8) * (G2:G10>0) * (G2:G10<>"") * 1)
and press CTRL+SHIFT+ENTER to validate the entry.

Here, i just added the (G2:G10<>"") part.

Regards,
Sebastien
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Thanks Sebastienm, your help was geratly appreciated. you did an excellent job.

Form Scotton
0
Commented:
Thank you very much, Scotton.

Sebastien
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Software

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.