COUNTIFS with on Dates

[TheKramer79]

Hello all,

I am trying to write a formula to count cells that fall within a date range that can change. I am setting the date range using the following: =CHOOSE(WEEKDAY(A1,1),A1-7,A1-8,A1-9,A1-10,A1-11,A1-12,A1-13) and =CHOOSE(WEEKDAY(A1,1),A1-1,A1-2,A1-3,A1-4,A1-5,A1-6,A1-7) where cell A1 is the user input cell. My problem is, in my COUNTIFS it will not recognize ANY reference to another cell for the date. I have even tried to type a date into another cell, ensuring that the cell is formatted the same as the report I am evaluating, and it still returns zero results. My COUNTIFS formula is =COUNTIFS('Outbound Log'!$C$2:$C$3000,">=1/19/2025",'Outbound Log'!$C$2:$C$3000,"<=1/25/2025",'Outbound Log'!$P$2:$P$3000,"=Shipped") where I need to replace the hard coded dates with cellular references.

 

[6StringJazzer]

You are using text, not actual dates. The simplest fix would be:

=COUNTIFS('Outbound Log'!$C$2:$C$3000,">="&DATEVALUE("1/19/2025"),'Outbound Log'!$C$2:$C$3000,"<="&DATEVALUE("1/25/2025"),'Outbound Log'!$P$2:$P$3000,"=Shipped")

Personally I would put the start and end dates into cells and reference the cells in the formula.

 

[Alex Blakenburg]

I need to replace the hard coded dates with cellular references

You mean something like this:

Book1
	A	B	C	D	E											P
1	31/01/2025			Start	19/01/2025
2				End	25/01/2025
3				Count	6
4
5
6			Date													Status
7			17/01/2025													Shipped
8			18/01/2025													Shipped
9			19/01/2025													Shipped
10			20/01/2025													Shipped
11			21/01/2025
12			22/01/2025													Shipped
13			23/01/2025													Shipped
14			24/01/2025													Shipped
15			25/01/2025													Shipped
16			26/01/2025													Shipped
17			27/01/2025													Shipped
18			28/01/2025													Shipped
19			29/01/2025													Shipped
20			30/01/2025													Shipped
21			31/01/2025													Shipped
22			01/02/2025													Shipped
23			02/02/2025													Shipped
24			03/02/2025													Shipped
25			04/02/2025													Shipped
Outbound Log

Cell Formulas
Range	Formula
E1	E1	=CHOOSE(WEEKDAY(A1,1),A1-7,A1-8,A1-9,A1-10,A1-11,A1-12,A1-13)
E2	E2	=CHOOSE(WEEKDAY(A1,1),A1-1,A1-2,A1-3,A1-4,A1-5,A1-6,A1-7)
E3	E3	=COUNTIFS('Outbound Log'!$C$2:$C$3000,">="&$E$1,'Outbound Log'!$C$2:$C$3000,"<="&$E$2,'Outbound Log'!$P$2:$P$3000,"Shipped")

 

[Cubist]

You can shorten the formulas in E1 and E2 like this:

Book1
	A	B	C	D	E
1	1/31/25			Start	1/19/25
2				End	1/25/25
3				Count	6
Outbound Log

Cell Formulas
Range	Formula
E1	E1	=INDEX(A1-SEQUENCE(7,,7),WEEKDAY(A1,1))
E2	E2	=INDEX(A1-SEQUENCE(7),WEEKDAY(A1,1))
E3	E3	=COUNTIFS('Outbound Log'!$C$2:$C$3000,">="&$E$1,'Outbound Log'!$C$2:$C$3000,"<="&$E$2,'Outbound Log'!$P$2:$P$3000,"Shipped")

 

[djclements]

Another alternative to CHOOSE for cells E1 and E2 on the sample mini-sheet:
E1: =E2-6
E2: =A1-WEEKDAY(A1)

 

[TheKramer79]

Thank you all for your help!!! I was able to implement several of your suggestions and have exactly what I need.

 

[Alex Blakenburg]

Thanks for the feedback, glad we could help.