CountIF from 2 rows...

[Newbie1974]

I have an excel spreadsheet that i need to count outputs.

It contains every case an individual has looked at and the date they looked at it...

I need to calculate the total one individual looked at in a date range.

What formula can i use to calculate this automatically.
I know how to calculate date range and name individually, but unsure how to put this altogether in one formula

 

[mrshl9898]

COUNTIFS is the solution.

You should have now =COUNTIF(range1,criteria1)

Just extend that with both like this:

=COUNTIFS(range1,criteria1,range2,criteria2)

 

[Toadstool]

Newbie1974.xlsx
	A	B	C	D	E	F
1	Individual	Case	Date		From	10-Sep-20
2	Fred	X54	09-Sep-20		To	21-Sep-20
3	Bert	X81	13-Sep-20		Name	Fred
4	Susan	X108	13-Sep-20		Count	2
5	Fred	X135	15-Sep-20
6	Bert	X162	17-Sep-20
7	Susan	X189	19-Sep-20
8	Fred	X216	21-Sep-20
9	Bert	X243	23-Sep-20
10	Susan	X270	25-Sep-20
Sheet1

Cell Formulas
Range	Formula
F4	F4	=COUNTIFS($A$2:$A$9999,$F$3,$C$2:$C$9999,">="&$F$1,$C$2:$C$9999,"<="&$F$2)

 

[Peter_SSs]

Another way ..

20 12 14.xlsm
	A	B	C	D	E	F
1	Individual	Case	Date		From	10-Sep-20
2	Fred	X54	09-Sep-20		To	21-Sep-20
3	Bert	X81	13-Sep-20		Name	Fred
4	Susan	X108	13-Sep-20		Count	2
5	Fred	X135	15-Sep-20
6	Bert	X162	17-Sep-20
7	Susan	X189	19-Sep-20
8	Fred	X216	21-Sep-20
9	Bert	X243	23-Sep-20
10	Susan	X270	25-Sep-20
Count

Cell Formulas
Range	Formula
F4	F4	=COUNT(FILTER(C2:C10,(C2:C10>=F1)*(C2:C10<=F2)*(A2:A10=F3)))

 

[Newbie1974]

Another way ..

20 12 14.xlsm
	A	B	C	D	E	F
1	Individual	Case	Date		From	10-Sep-20
2	Fred	X54	09-Sep-20		To	21-Sep-20
3	Bert	X81	13-Sep-20		Name	Fred
4	Susan	X108	13-Sep-20		Count	2
5	Fred	X135	15-Sep-20
6	Bert	X162	17-Sep-20
7	Susan	X189	19-Sep-20
8	Fred	X216	21-Sep-20
9	Bert	X243	23-Sep-20
10	Susan	X270	25-Sep-20
Count

Cell Formulas
Range	Formula
F4	F4	=COUNT(FILTER(C2:C10,(C2:C10>=F1)*(C2:C10<=F2)*(A2:A10=F3)))

What is the(FILTER) for?

 

[Peter_SSs]

What is the(FILTER) for?

FILTER is one of the new dynamic array functions that is available in Excel 365. I have used it to extract the rows that meet your criteria so that those rows can be counted to get your required result.