Counting number of times an age appears, when calculated from DOB formula used

[MrMac80]

HI Team,

In cell A1 there is the DATE ENTERED of the record. In B1 there is a DOB, in Cell C1 is a formula to calculate the Age of that DOB person Today (this works and is therefore ever changing, which is important). This is repeated for many many rows of different people.

Now i want to count how many 10yo people there are in the worksheet that were entered between 2 specific dates. I cant work out how to do this

I have been able to get counting how many blue eyed people where entered between 2 certain dates using =SUMPRODUCT(--('Worksheet'!$A$1:$A$1553>=Summary!$D$6),--('Worksheet'!$A$1:$A$1553<=Summary!$D$7),--('Worksheet'!$L$1:$L$1553="Blue Eyes")), where the dates i want to look between are entered into Summary!$D$6 (start date) and Summary!$D$7 (end date).

I think my problem is that the formula used to calculate the age stays in the background of the cell, and therefore looking for "10" doesnt work (i.e. using the same formula as above, and changing the range to look in and using "10" instead of "Blue Eyes"). Am i right?

If so, can someone please advise how i do it. I have considered is there a way to add in another column and have the age calculating formula in there, and have the result be entered into the next cell, meaning that a value is entered rather than a formula in the background, does this work, and if so how? Or is there a better way.

Please help!?!?

Thank you.

example attached.

[BMV]

Your mistake is

Please Login or Register  to view this content.

It could be

Please Login or Register  to view this content.

But best way is

Please Login or Register  to view this content.

and there is no difference

Please Login or Register  to view this content.

[MrMac80]

Thanks for looking,

I cant seem to get any of your options to work. At first i thought it was because my example was super simple, because when i tried your formulas on my example one, they WORKED. But when i increase the number of cells it needs to reference it breaks. Does this make sense.

No matter what i enter from your examples i get a #value error.

[protonLeah]

To make your formulas draggable modify as such:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Note that you need absolute cell addresses for $A$1:$B$6

---

The data range is on separate sheets and not next to each other like in this example.

That's why you must post truly representative example files...

[MrMac80]

Sorry, working with sensitive data, so didnt want to make a mistake. My example was too simple.

I have done my best with this one, by using an old sheet, and removing all details, except the important ones. I hope this is better.

[BMV]

=COUNTIFS('2020 EDT Youth Data '!$B$4:$B$999;">="&$D$6;'2020 EDT Youth Data '!$B$4:$B$999;"<="&$D$7;'2020 EDT Youth Data '!$H$4:$H$999;SUBSTITUTE(Summary!C15;"yo";""))

[MrMac80]

HI BMV, that didnt work. "There's a problem with this formula" error. It highlighted "$B$4:$B$999" any ideas.

This is what i couldnt work out. Not sure why this is not working

EDIT: i couldnt work out why it wasnt working, then i changed the ; to , and it worked

Thank you all

[protonLeah]

$H$240:$H$999, should be $H$4:$H$999