COUNTIFS with DATEVALUE

[Satyen]

Hi,

I have a column wherein column A states the name of the airline and column B states the flight departure date. However, column B has some text along with the date. Example:

Airline	ETD
United	Flight departed on 01-Mar-23
United	Flight departed on 02-Mar-23
Lufthansa	Flight departed on 03-Mar-23
United	Flight departed on 23-Mar-23
Delta	Flight departed on 24-Mar-23
United	Flight departed on 25-Mar-23
United	Flight departed on 01-Apr-23
Delta	Flight departed on 03-Apr-23
Lufthansa	Flight departed on 05-Apr-23

I would like to count the number of cells wherein the airline is "United" and the departure date is before 01-Apr-23.

How can I use COUNTIFS along with DATEVALUE? or if there is any better way to get the answer?

 

[RudRud]

Hi, while waiting for other experts, I will share my thought here

Book5
	A	B	C	D	E	F	G	H
1	United	Flight departed on 01-Mar-23		3/1/23		United	4/1/23	4
2	United	Flight departed on 02-Mar-23		3/2/23
3	Lufthansa	Flight departed on 03-Mar-23		3/3/23
4	United	Flight departed on 23-Mar-23		3/23/23
5	Delta	Flight departed on 24-Mar-23		3/24/23
6	United	Flight departed on 25-Mar-23		3/25/23
7	United	Flight departed on 01-Apr-23		4/1/23
8	Delta	Flight departed on 03-Apr-23		4/3/23
9	Lufthansa	Flight departed on 05-Apr-23		4/5/23
Sheet1

Cell Formulas
Range	Formula
H1	H1	=COUNTIFS(A:A,F1,D:D,"<"&G1)
D1:D9	D1	=DATEVALUE(SUBSTITUTE(B1,"Flight departed on ",""))

FYI

 

[Satyen]

Thanks. Unfortunately, I am not allowed to tamper with the master data by including another column for DATAVALUE. Need one formula that includes COUNTIFS and DATAVALUE.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1	Airline	ETD			01/04/2023
2	United	Flight departed on 01-Mar-23		United	4
3	United	Flight departed on 02-Mar-23
4	Lufthansa	Flight departed on 03-Mar-23
5	United	Flight departed on 23-Mar-23
6	Delta	Flight departed on 24-Mar-23
7	United	Flight departed on 25-Mar-23
8	United	Flight departed on 01-Apr-23
9	Delta	Flight departed on 03-Apr-23
10	Lufthansa	Flight departed on 05-Apr-23
Dashboard

Cell Formulas
Range	Formula
E2	E2	=LET(f,FILTER(--(TEXTAFTER(B2:B20," ",-1)),A2:A20=D2),COUNT(FILTER(f,f<E1)))

 

[RudRud]

How about

Fluff.xlsm
	A	B	C	D	E
1	Airline	ETD			01/04/2023
2	United	Flight departed on 01-Mar-23		United	4
3	United	Flight departed on 02-Mar-23
4	Lufthansa	Flight departed on 03-Mar-23
5	United	Flight departed on 23-Mar-23
6	Delta	Flight departed on 24-Mar-23
7	United	Flight departed on 25-Mar-23
8	United	Flight departed on 01-Apr-23
9	Delta	Flight departed on 03-Apr-23
10	Lufthansa	Flight departed on 05-Apr-23
Dashboard

Cell Formulas
Range	Formula
E2	E2	=LET(f,FILTER(--(TEXTAFTER(B2:B20," ",-1)),A2:A20=D2),COUNT(FILTER(f,f<E1)))

Hi @Fluff , May I know what does -- mean in the formula? Thankyou

As I try to understand the formula and If I delete -- it will become 0.

 

[Fluff]

It's called a double unary and converts a number stored as text into a real number.