Using Function in Criteria Range of DCOUNT

[cfcaec]

Hi

This is my first post, so please bear with me. I've gone through all the microsoft support pages etc... etc.. No luck.

I have created a register that gives the ages of the participants being registered. I need to figure out the number of boys and girls of different age groups. I used the formula =DCOUNT(database,column no,criteria) with criteria having two columns, Gender & Date of Birth. Under Gender was M, Under Date of Birth was >=01/03/2002 [dd/mm/yyyy]. This gave perfect results shwing me the number of boys less than 12 years old. However, wishing to make the formula dynamic I changed it to >=EDATE(TODAY(),-144) which is exactly twelve years ago. This formula gives wrong results and I can't understand why.

Can any one help me?

Thank you.

Tony

 

[Aladin Akyurek]

Thanks everyone. Solved it.

I wanted the table to be dynamic. That is as the months go by the table will keep updating the number of G (girls) and B (boys) of different age groups. The actual database keeps informing me of the number of girls and boys in different age categories (below 12, 12 to 16, 16 to 19, 19 to 21, 21 and above) as on date the file is opened. The example given just identifies the girls and boys below 12. So C7 should show G < 12: 1 (1 girl below 12) and C8 should show B < 12: 2 (2 boys below 12).

The only way to do that is to use EDATE(start date, months). Somehow using EDATE in the criteria row does not work. If I put the actual dates it works. However, that means I have to update the formula each time I open the file, say after 1 year.

Thanks to your inputs I tried putting =">="&EDATE(TODAY(),-144) in C3 and it worked like a charm.

Haven't figured out why >=01/03/2002 works, but >=EDATE(TODAY(),-144) does not but =">="&EDATE(TODAY(),-144) works!

Thanks all the same. Can sleep peacefully tonight.

Good night and God bless.

Tony

You are welcome. If we don't set up the criteria range as suggested, we get a count of 5, that is, all dates are counted.

You could also invoke non-database functions for such counts:

=COUNTIFS($A$5:$A$9,"M",$B$5:$B$9,">="&$A$1)

=COUNTIFS($A$5:$A$9,"F",$B$5:$B$9,">="&$A$1)