Countifs Question

[julesrapley]

Hi all

I have one workbook with 2 worksheets. The first sheet (Database) has 3 columns: Date of Birth, Age, Start Date. The second sheet (Stats) has a start date of 01/04/2023 in cell B2 and End date of 31/03/2025 in cell B3. On the same sheet I have headings: 16 - 19, 20 - 24, 24 - 29, and 50+. I need Excel to total the number of each age range and to automatically update no matter what I change the Start Date/End Date to on the Stats sheet.

 

[Marius44]

HI
I think it would be better to attach your file (without sensitive data).
HI,
Mario

 

[julesrapley]

Unfortunately, my system won't allow me to install anything on my laptop - sorry.

 

[Cubist]

Your data doesn't have End Date

 

[julesrapley]

There is a start date in cell b1 and end date in cell b2 of the Stats worksheet. These dates will change depending on the report required. Eg if I know to know how many 50+ people were enrolled between 1st and 31st July 2024.

 

[Sergius]

Hello! It proceeded from the fact that on the Database sheet is a smart table with the name Table1, then for the Stats sheet

Book1.xlsm
	B	C
1	01.04.2023
2	31.03.2025
3
4
5
6
7
8	16-19	0
9	20-24	0
10	25-49	0
11	50+	0
Stats

Cell Formulas
Range	Formula
C8:C10	C8	=SUMPRODUCT((Table1[Start date]>=$B$1)*(Table1[Start date]<=$B$2)*(Table1[Age]>=LEFT(B8,FIND("-",B8)-1)+0)*(Table1[Age]<=RIGHT(B8,LEN(B8)-FIND("-",B8))+0))
C11	C11	=SUMPRODUCT((Table1[Start date]>=$B$1)*(Table1[Start date]<=$B$2)*(Table1[Age]>=LEFT(B11,FIND("+",B11)-1)+0))

 

[Cubist]

Are you open to having the age bands broken out into individual cells (like below) instead of together? The formula would be simpler and more efficient.

Book1
	B	C
8	16	19
9	20	24
10	25	49
11	50
Sheet3

 

[julesrapley]

Hi. I don't mind. As long as the result is for the combined number of people within that age range. Would be happy to have a 3rd sheet that had the "workings" if needed, and then the results if that copied onto the Stats sheet.