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]

Care to post a tiny sample?

 

[cfcaec]

I forgot to mention that I use Excel 2007 on Window 7 Home Basic. Also the formula =EDATE(TODAY(),-144) evaluates perfectly elsewhere.
Thanks
Tony

 

[cfcaec]

I really do not know how to post a sample.
Tony

 

[Aladin Akyurek]

I really do not know how to post a sample.
Tony

http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

Remember:just a tiny sample.

 

[cfcaec]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]GENDER[/TD]
[TD="align: center"]DATE OF BIRTH[/TD]
[TD="align: center"]GENDER[/TD]
[TD="align: center"]DATE OF BIRTH[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]>=EDATE(TODAY(),-144)[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]>=EDATE(TODAY(),-144)[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]GENDER[/TD]
[TD="align: center"]DATE OF BIRTH[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]16/05/1986[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]12/02/2006[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]15/04/2007[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]15/04/2007[/TD]
[TD="align: center"]="G < 12: "&DCOUNT(A3:B8,2,$C$1:$D$2)[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]01/04/1984[/TD]
[TD="align: center"]="B < 12: "&DCOUNT(A3:B8,2,$A$1:$B$2)[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

Remember:just a tiny sample.

I will try and reproduce it here as all my attempts at copying and pasting have failed.

The answer in C7 & C8 come out wrong.

Thanks for all the help.

Tony

 

[Aladin Akyurek]

Just for the sale of completeness: What are the expected outcomes (answers) in C7 and C8?

 

[Aladin Akyurek]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]GENDER[/TD]
[TD="align: center"]DATE OF BIRTH[/TD]
[TD="align: center"]GENDER[/TD]
[TD="align: center"]DATE OF BIRTH[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]>=EDATE(TODAY(),-144)[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]>=EDATE(TODAY(),-144)[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]GENDER[/TD]
[TD="align: center"]DATE OF BIRTH[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]16/05/1986[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]12/02/2006[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]15/04/2007[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]15/04/2007[/TD]
[TD="align: center"]="G < 12: "&DCOUNT(A3:B8,2,$C$1:$D$2)[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]01/04/1984[/TD]
[TD="align: center"]="B < 12: "&DCOUNT(A3:B8,2,$A$1:$B$2)[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


I will try and reproduce it here as all my attempts at copying and pasting have failed.

The answer in C7 & C8 come out wrong.

Thanks for all the help.

Tony

Just for the sale of completeness: What are the expected outcomes (answers) in C7 and C8?

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]3/1/2002[/TD]
[TD="width: 99"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="width: 94"]GENDER[/TD]
[TD="width: 99"]DATE OF BIRTH[/TD]
[TD="width: 64"]GENDER[/TD]
[/TR]
[TR]
[TD="width: 94"]=M[/TD]
[TD="width: 99"]>=37316[/TD]
[TD="width: 64"]=F[/TD]
[/TR]
[TR]
[TD="width: 94"]GENDER[/TD]
[TD="width: 99"]DATE OF BIRTH[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="width: 94"]F[/TD]
[TD="width: 99"]5/16/1986
[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="width: 94"]M[/TD]
[TD="width: 99"]2/12/2006[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="width: 94"]M[/TD]
[TD="width: 99"]4/15/2007[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="width: 94"]F[/TD]
[TD="width: 99"]4/15/2007[/TD]
[TD="width: 64"]G < 12: 2
[/TD]
[/TR]
[TR]
[TD="width: 94"]M[/TD]
[TD="width: 99"]4/1/1984[/TD]
[TD="width: 64"]B < 12: 1
[/TD]
[/TR]
</tbody>[/TABLE]

A1:

=EDATE(TODAY(),-144)

A3:

="=M"

B3:

=">="&$A$1

C3:

="=F"

C8: (Involves gender M)

="G < 12: "&DCOUNTA($A$4:$B$9,"GENDER",$A$2:$B$3)

C9: (involves gender F)

="B < 12: "&DCOUNTA($A$4:$B$9,"GENDER",$B$2:$C$3)

By the way, what are G's and B's?

 

[cfcaec]

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

 

[markmzz]

You can try this too:

=">="&TEXT(EDATE(TODAY(),-144),"dd/mm/yyyy")

Result

[TABLE="width: 183"]
<tbody>[TR]
[TD="width: 47, bgcolor: transparent"]GENDER[/TD]
[TD="width: 77, bgcolor: transparent"]DATE OF BIRTH[/TD]
[TD="width: 47, bgcolor: transparent"]GENDER[/TD]
[TD="width: 73, bgcolor: transparent"]DATE OF BIRTH[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]M[/TD]
[TD="bgcolor: yellow"]>=01/03/2002[/TD]
[TD="bgcolor: transparent"]F[/TD]
[TD="bgcolor: yellow"]>=01/03/2002[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]GENDER[/TD]
[TD="bgcolor: transparent"]DATE OF BIRTH[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]F[/TD]
[TD="bgcolor: transparent, align: right"]16/05/1986[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]M[/TD]
[TD="bgcolor: transparent, align: right"]12/02/2006[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]M[/TD]
[TD="bgcolor: transparent, align: right"]15/04/2007[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]F[/TD]
[TD="bgcolor: transparent, align: right"]15/04/2007[/TD]
[TD="bgcolor: yellow"]B < 12: 2[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]M[/TD]
[TD="bgcolor: transparent, align: right"]01/04/1984[/TD]
[TD="bgcolor: yellow"]G < 12: 1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]********[/TD]
[TD="bgcolor: transparent"]**************[/TD]
[TD="bgcolor: transparent"]********[/TD]
[TD="bgcolor: transparent"]*************[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz