Countifs between dates by name

[kxellis26]

Greetings -

I built a sheet that shows me how many times each of my employees made entries in a specific tab, based on how many times they entered a date between a specific range. The formula I have now works, but it's very manual and I'd rather be able to choose the date range by simply changing it in 2 cells. I have tried to change my formula using "<="&C1 and ">="&D1, but it doesn't return anything. I can't figure out why it's not working. Any help would be appreciated.

Master Call Listening Tracker 2023.xlsx
	B	C	D
2	Date Range	5/1/2023	8/8/2023
3
4	Andrew	0
5	Andrew K	0
6	Anthony	0
7	Asa	0
8	Brandon	0
9	Chad	0
10	Christie	19
11	Cody	0
12	CJ	0
13	Evan	0
14	Glenn	0
15	James	0
16	Josiah	0
17	Justin	0
18	Karen	0
19	Kelly	0
20	Kyle	3
21	Logan	0
22	Matt	0
23	Megan	0
24	Miranda	0
25	Morgan	0
26	Ryan	0
27	Sandra	0
28	Stephanie	0
29	Tom	0
30	Tyler	0
Weekly Tracking

Cell Formulas
Range	Formula
C4:C30	C4	=COUNTIFS(Date_Tracked,">=8/1/2023",Date_Tracked,"<=8/8/2023",Observer,'Weekly Tracking'!B4)

Named Ranges
Name	Refers To	Cells
Date_Tracked	='Call Study - Tech'!$E$3:$E$1048576	C4:C30
Observer	='Call Study - Tech'!$D$3:$D$1048576	C4:C30

 

[Jeffrey Mahoney]

Well I think you're using C1 & C1 instead of C2 and D2

I recreated your formula, almost, and got a result that I think works
=COUNTIFS(Sheet6!$B$2:$B$5001,Sheet5!$B4,Sheet6!$C$2:$C$5001,">="&Sheet5!C$2,Sheet6!$C$2:$C$5001,"<="&Sheet5!D$2)

FAPB48 20230808.xlsm
	B	C	D	E
2	Date Range	5/1/2023	8/8/2023	11/15/2023
3
4	Andrew	57	53
5	Andrew K	57	53
6	Anthony	54	53
Sheet5

Cell Formulas
Range	Formula
C4:D6	C4	=COUNTIFS(Sheet6!$B$2:$B$5001,Sheet5!$B4,Sheet6!$C$2:$C$5001,">="&Sheet5!C$2,Sheet6!$C$2:$C$5001,"<="&Sheet5!D$2)

 

[Fluff]

I have tried to change my formula using "<="&C1 and ">="&D1

You have it the wrong way round, it should ">="&C$1 "<="&D$1
Also (as has been said ) from the mini-sheet it should maybe be C2 & D2
Finally never refer to the sheet name the formula is on as it can cause problems.

=COUNTIFS(Date_Tracked,">="&C2,Date_Tracked,"<="&D$2,Observer,B4)

 

[kxellis26]

You have it the wrong way round, it should ">="&C$1 "<="&D$1
Also (as has been said ) from the mini-sheet it should maybe be C2 & D2
Finally never refer to the sheet name the formula is on as it can cause problems.

=COUNTIFS(Date_Tracked,">="&C2,Date_Tracked,"<="&D$2,Observer,B4)

Wow. What a fool I am! I updated and locked the cells. Now it works perfectly.

Also, I added the row right before I sent this, so it was actually C1 & D1. My bad!

Either way, thank you so much for the assist!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[kxellis26]

You have it the wrong way round, it should ">="&C$1 "<="&D$1
Also (as has been said ) from the mini-sheet it should maybe be C2 & D2
Finally never refer to the sheet name the formula is on as it can cause problems.

=COUNTIFS(Date_Tracked,">="&C2,Date_Tracked,"<="&D$2,Observer,B4)

Thank you for the assist!