Countifs based on range defined by date

[Deven09]

I have data sheet format as below.

A B C
1 Name | 06/01/2023 | 06/02/2023 and so on for all dates.
2 Sam 2 3
3 Sam 0 2
4 Dan 2 3
5 Sam 4 0
6 Ram 3 1

I need output as below. Need countifs formula which will take counts with conditions as per date entered in first row. If the dates are changed then the count should be taken from the respective date column of the data sheet.

A B C
1 06/01/2023 | 06/01/2023 and so on for all dates.
2 Name count of records<3 | count of records Between 3 to 5
3 Sam <Formula> =2 | <Formula> =1
4 Ram <Formula> = 0 | <Formula> = 1
5 Dan <Formula> = 1 | <Formula> =0

 

[Deven09]

Refer attached image.

 

[kweaver]

Is this what you mean?

Book1
	A	B	C	D	E	F	G
1	Name	Date 1	Date 2		Result	Date 1	Date 2
2	Sam	2	3		Sam	2 | 1	2 | 1
3	Sam	0	2		Ram	0 | 1	1 | 0
4	Dan	2	3		Dan	1 | 0	0 | 1
5	Sam	4	0
6	Ram	3	1
Sheet2

Cell Formulas
Range	Formula
F2:G4	F2	=COUNTIFS($A$2:$A$6,$E2,B$2:B$6,"<3")&" | "&COUNTIFS($A$2:$A$6,$E2,B$2:B$6,">=3",B$2:B$6,"<=5")

 

[kweaver]

BTW: your posted picture is small enough to replicate. In the future, especially with large samples of data, please use XL2BB to post sample data and expected results.

 

[kweaver]

So, how about this modification?

Book1
	A	B	C	D	E	F	G	H	I
1	Name	Date 1	Date 2		Result	Date 1		Date 2
2	Sam	2	3		Sam	2	1	2	1
3	Sam	0	2		Ram	0	1	1	0
4	Dan	2	3		Dan	1	0	0	1
5	Sam	4	0
6	Ram	3	1
Sheet2

Cell Formulas
Range	Formula
F2:F4	F2	=COUNTIFS($A$2:$A$6,$E2,B$2:B$6,"<3")
G2:G4	G2	=COUNTIFS($A$2:$A$6,$E2,B$2:B$6,">=3",B$2:B$6,"<=5")
H2:H4	H2	=COUNTIFS($A$2:$A$6,$E2,C$2:C$6,"<3")
I2:I4	I2	=COUNTIFS($A$2:$A$6,$E2,C$2:C$6,">=3",C$2:C$6,"<=5")

 

[Deven09]

So, how about this modification?

Book1
	A	B	C	D	E	F	G	H	I
1	Name	Date 1	Date 2		Result	Date 1		Date 2
2	Sam	2	3		Sam	2	1	2	1
3	Sam	0	2		Ram	0	1	1	0
4	Dan	2	3		Dan	1	0	0	1
5	Sam	4	0
6	Ram	3	1
Sheet2

Cell Formulas
Range	Formula
F2:F4	F2	=COUNTIFS($A$2:$A$6,$E2,B$2:B$6,"<3")
G2:G4	G2	=COUNTIFS($A$2:$A$6,$E2,B$2:B$6,">=3",B$2:B$6,"<=5")
H2:H4	H2	=COUNTIFS($A$2:$A$6,$E2,C$2:C$6,"<3")
I2:I4	I2	=COUNTIFS($A$2:$A$6,$E2,C$2:C$6,">=3",C$2:C$6,"<=5")

Thanks but the data will be for entire month, with this solution, I need to revise formula for each date. Is it possible to have a formula for one date(Column F and Column G) which can be simply copy pasted to another dates. One solution I was thinking of to design a formula which will decide the range based on date mentioned in the headings and accordingly populate counts based on dates.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Name	01/06/2023	02/06/2023		Result	01/06/2023	01/06/2023	02/06/2023	02/06/2023
2	Sam	2	3		Sam	2	1	2	1
3	Sam	0	2		Ram	0	1	1	0
4	Dan	2	3		Dan	1	0	0	1
5	Sam	4	0
6	Ram	3	1
7
Sheet4

Cell Formulas
Range	Formula
F2:F4,H2:H4	F2	=SUMPRODUCT(($A$2:$A$6=$E2)*($B$1:$C$1=F$1)*($B$2:$C$6<3))
G2:G4,I2:I4	G2	=SUMPRODUCT(($A$2:$A$6=$E2)*($B$1:$C$1=G$1)*($B$2:$C$6>=3)*($B$2:$C$6<=5))

 

[Deven09]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Name	01/06/2023	02/06/2023		Result	01/06/2023	01/06/2023	02/06/2023	02/06/2023
2	Sam	2	3		Sam	2	1	2	1
3	Sam	0	2		Ram	0	1	1	0
4	Dan	2	3		Dan	1	0	0	1
5	Sam	4	0
6	Ram	3	1
7
Sheet4

Cell Formulas
Range	Formula
F2:F4,H2:H4	F2	=SUMPRODUCT(($A$2:$A$6=$E2)*($B$1:$C$1=F$1)*($B$2:$C$6<3))
G2:G4,I2:I4	G2	=SUMPRODUCT(($A$2:$A$6=$E2)*($B$1:$C$1=G$1)*($B$2:$C$6>=3)*($B$2:$C$6<=5))

Perfect...!!!! You are a great help..!! I was struggling with this.. Thanks a lot..!!!

 

[Fluff]

Glad we could help & thanks for the feedback.