CountIF Date Range and Criteria in different Sheet

[PCloadletter]

Hello,

I'd like help with a formula in counting a specific date range and criteria on a different sheet.

Example :

Sheet 1 is named Complaints and will have in column A all the dates
in column C of Complaints will be a range of locations, RH for example.

In Sheet 2, I'd like to be able to count all the "RH"s that appear between a certain date range (eg : September 1st, 2020 to September 30th, 2020)

Thank you in advance for any help!

 

[etaf]

=COUNTIFS(Sheet1!A:A,">=1/9/2020",Sheet1!A:A,"<=30/9/2020",Sheet1!B:B,"RH")

Or you could put the criteria in sheet 2
Sheet 1

Book3
	A	B
1	Date	Region
2	9/1/20
3	9/2/20	RH
4	9/3/20
5	9/4/20
6	9/5/20	RH
7	9/6/20
8	9/7/20
9	9/8/20
10	9/9/20
11	9/10/20
12	9/11/20
13	9/12/20
14	9/13/20
15	9/14/20	RH
16	9/15/20
17	9/16/20
18	9/17/20
19	9/18/20
20	9/19/20
21	9/20/20
22	9/21/20
23	9/22/20
24	9/23/20
Sheet1

Sheet 2

Book3
	A	B	C	D	E	F
1	Region	Date Start	Date End	Count	OR	Count Criteria
2	RH	9/1/20	9/16/20	3		3
Sheet2

Cell Formulas
Range	Formula
D2	D2	=COUNTIFS(Sheet1!A:A,">=1/9/2020",Sheet1!A:A,"<=30/9/2020",Sheet1!B:B,"RH")
F2	F2	=COUNTIFS(Sheet1!A:A,">="&B2,Sheet1!A:A,"<="&C2,Sheet1!B:B,A2)

 

[PCloadletter]

Thank you Etaf, odd - the first formula for D2 doesn't work but the F2 one does.

For D2 it just returns a 0.

 

[etaf]

you only need to use 1 , and F2 is better as you can change the variables in the cell rather than in the formula
Not quite sure why its not working on your version of excel 365 same as mine

 

[etaf]

Possibly a format issue
you could try =COUNTIFS(Sheet1!A:A,">="&DATEVALUE("1/9/2020"),Sheet1!A:A,"<="&DATEVALUE("30/9/2020"),Sheet1!B:B,"RH") and use the date format for your excel / region settings

 

[PCloadletter]

Thanks again Etaf! I didn't realize how picky Excel was...I changed the dates in the formula to 1/SEP/2020 and it worked!

 

[etaf]

you are welcome