Last matching Value in multiple Columns

[Emre06]

Hello, i have 2 Columns containing guards's names. ( I tried to use "maxifs" function but it is working only for one column ) My Question is: I need formula to find "Last Date" of "John Fizz" in "Beach"?

Kitap1 (version 1).xlsb
	A	B	C	D
1	Date	Guard 1	Guard 2	Location
2	1.02.2023	Mike Rogers	Romeo Loi	Lobby
3	2.02.2023	John Fizz	Taric Ben	Beach
4	3.02.2023	Alan Dusk	Lora Ian	Hotel
5	4.02.2023	Clark Kent	John Fizz	Beach
Sayfa1

 

[BigBeachBananas]

Change MAX to COUNTA

=COUNTA(FILTER(A:A,((B:B= "John FIzz")+(C:C="John Fizz"))*ISNUMBER(SEARCH("Beach",D:D))))

 

[Emre06]

Change MAX to COUNTA

=COUNTA(FILTER(A:A,((B:B= "John FIzz")+(C:C="John Fizz"))*ISNUMBER(SEARCH("Beach",D:D))))

Thank you, but having a problem now. John fizz is 2 times in beach and counting correctly but, it is also counting 1 for others who hasn't been in beach.

 

[BigBeachBananas]

My mistake, the error will always return a count of 1. How about this?

=SUMPRODUCT(--NOT(ISERROR(FILTER(A:A,((B:B="John Fizz")+(C:C="John Fizz"))*ISNUMBER(SEARCH("Beach",D:D))))))

 

[Emre06]

My mistake, the error will always return a count of 1. How about this?

=SUMPRODUCT(--NOT(ISERROR(FILTER(A:A,((B:B="John Fizz")+(C:C="John Fizz"))*ISNUMBER(SEARCH("Beach",D:D))))))

Thank you very much, customization took very long but it is working now.

 

[Alex Blakenburg]

FYI - SumProduct will make in backward compatible but in Excel 2021 and greater (incl MS365) just Sum will work.

 

[Emre06]

Try this. I have also rearranged it a little so you don't need to evaluate the location twice.

=MAX(FILTER(A:A,((B:B="John Fizz")+(C:C="John Fizz"))*ISNUMBER(SEARCH("Beach",D:D))))

Hello again, now I am having a new problem. John Fizz can be in multiple places now on the same day (Beach, Hotel), but I just wanted to find the beach version. If I use I wanted to use Wildcard but the filter giving error, how can I solve this ?

 

[Alex Blakenburg]

I don't understand what is different.
This ISNUMBER(SEARCH("Beach",D:D)), is the equivalent of looking for *Beach*
So what is it not doing ?