Extend COUNTIFS

[Bandito1]

Hello,

I use the following formula to count the incoming files that are cancelled;


=COUNTIFS('[Registratie review.xlsx]2016-2022'!$H:$H;">="&B12;'[Registratie review.xlsx]2016-2022'!$F:$F;"Cancelled";'[Registratie review.xlsx]2016-2022'!$H:$H;"<="& EOMONTH(B12;0))

The problem;

The reason is never alone "Cancelled"

It can be Cancelled because of A
Cancelled because of C

And so on.

How can i make it that it only looks to the word Cancelled and count those what ever is after cancelled?

 

[Fluff]

How about

=COUNTIFS('[Registratie review.xlsx]2016-2022'!$H:$H;">="&B12;'[Registratie review.xlsx]2016-2022'!$F:$F;"Cancelled*";'[Registratie review.xlsx]2016-2022'!$H:$H;"<="& EOMONTH(B12;0))

 

[JamesCanale]

Switch to sumproduct, that handles non-ranges

MrExcelPlayground13.xlsx
	B	C	D	E	F	G	H
1	2				Okay		10/16/2022
2					Cancelled for A		10/16/2022
3					Okay		10/16/2022
4					Cancelled for B		10/16/2022
5					Good		10/16/2022
6					starting soon		10/16/2022
7					Cancelled for A		11/1/2022
8					Cancelled		11/1/2022
9					Nice		11/1/2022
10					Stop		11/1/2022
11
12	10/15/2022
Sheet13

Cell Formulas
Range	Formula
B1	B1	=SUMPRODUCT(--(H1:H10>=B12),--(H1:H10<=EOMONTH(B12,0)),--(LEFT(F1:F10,9)="Cancelled"))

 

[Bandito1]

is Sumproduct better than COUNTIFS?

Got some problems understanding the SUMPRODUCT formula

 

[Fluff]

is Sumproduct better than COUNTIFS?

No in my opinion, Countifs is more efficient.
Did you try the suggestion I made?

 

[Bandito1]

Yes, it seems like it works.

One problem that I'm facing, which makes it hard to test;

Im pulling data from "Registratie Review".
This file is password protected.

When I close this file all data in the file that pulls data from Registratie Review turns to #VALUE

Is this because the files is password protected?
Is their a way to solve this?

 

[Fluff]

Countifs does not work with a closed file, you would need to switch to sumproduct instead