Use * in formula

[Bandito1]

Hi all,

I use this formula to find files that are cancelled;

=SUMPRODUCT(('\\nltilnetapsnas1\PUBLIC\Production Support\[Registratie review.xlsx]2016-2022'!$H:$H>=B29)*('\\nltilnetapsnas1\PUBLIC\Production Support\[Registratie review.xlsx]2016-2022'!$H:$H<=EOMONTH(B29;0))*('\\nltilnetapsnas1\PUBLIC\Production Support\[Registratie review.xlsx]2016-2022'!$J:$J="Cancelled"))

Only the thing is that the comment is not always exactly Cancelled

It can be "Cancelled; because of xx"

Normally i can use Cancelled*
Then it doesn't matter what follows after cancelled.

But in formula when i do Cancelled* it search for exactly that.

Is there a way to make it like it searches for Cancelled and whatever follows?

 

[Fluff]

How about

=SUMPRODUCT(('\\nltilnetapsnas1\PUBLIC\Production Support\[Registratie review.xlsx]2016-2022'!$H:$H>=B29)*('\\nltilnetapsnas1\PUBLIC\Production Support\[Registratie review.xlsx]2016-2022'!$H:$H<=EOMONTH(B29;0))*(ISNUMBER(SEARCH("Cancelled";'\\nltilnetapsnas1\PUBLIC\Production Support\[Registratie review.xlsx]2016-2022'!$J:$J))))

 

[Bandito1]

Wauw, that's what i call a fast reply
And it works, thanks!

 

[Fluff]

You're welcome & thanks for the feedback.