Excluding in MAXIFS

[Austin Lang]

Hello all! I have scoured the forum, but I haven't seen anybody with a similar issue.

I have a table similar to the one below. I am using the two formulas below to return the most recent cleaning done to this particular piece of equipment on a separate tab. The first one returns the date, and the second one returns the time. Is there a way to add to these formulas to exclude that particular cleaning if "Yes" is displayed in column W next to it? In this example, the formulas are returning the information from row 11, but I would like it to be returning row 10 because of the "yes".

=MAXIFS(Sheet1!$R:$R,Sheet1$A:$A,Sheet2!AC44)
=MAXIFS(Sheet1!$T:$T,Sheet1$A:$A, "Fridge 1",Sheet1!$R:$R,AD44)

Thank you in advance!

 

[Austin Lang]

Hello all! I have scoured the forum, but I haven't seen anybody with a similar issue.

I have a table similar to the one below. I am using the two formulas below to return the most recent cleaning done to this particular piece of equipment on a separate tab. The first one returns the date, and the second one returns the time. Is there a way to add to these formulas to exclude that particular cleaning if "Yes" is displayed in column W next to it? In this example, the formulas are returning the information from row 11, but I would like it to be returning row 10 because of the "yes".

=MAXIFS(Sheet1!$R:$R,Sheet1$A:$A,Sheet2!AC44)
=MAXIFS(Sheet1!$T:$T,Sheet1$A:$A, "Fridge 1",Sheet1!$R:$R,AD44)

View attachment 46631

Thank you in advance!

Correction... Second formula reads .."Fridge Unit 1"..

 

[FormR]

Hi, welcome to the forum. You can add the extra criteria like this.

=MAXIFS(Sheet1!$R:$R,Sheet1!$A:$A,Sheet2!AC44,Sheet1!$W:$W,"<>Yes")

 

[Austin Lang]

Hi, welcome to the forum. You can add the extra criteria like this.

=MAXIFS(Sheet1!$R:$R,Sheet1!$A:$A,Sheet2!AC44,Sheet1!$W:$W,"<>Yes")

Thank you so much for the quick response! I had tried something similar, but I must have had a small syntax error somewhere.

 

[Alex Blakenburg]

I will be interested to know if that really does work for you.
I imagine that the Date is related to the Start Time.

So in the scenario that you have
2 Sept 21 14:00 to 18:00
2 Sept 21 22:00 to 2:00
The Max End Time for 2 Sept 21 is going to return 18:00.
However wouldn't the Max End Time really be 3 Sept 2am ?

 

[Austin Lang]

I will be interested to know if that really does work for you.
I imagine that the Date is related to the Start Time.

So in the scenario that you have
2 Sept 21 14:00 to 18:00
2 Sept 21 22:00 to 2:00
The Max End Time for 2 Sept 21 is going to return 18:00.
However wouldn't the Max End Time really be 3 Sept 2am?

I am going to be adding the new formulas to the working document in the next day or so. I have been testing on a test sheet for now. Everything seems to function. We have seen that issue a few times on the working document, though. The team has been temporarily entering the date as the next day so that there isn't confusion. That is another item on the to-do list... Luckily, the cleanings are 2 hours at the most. It isn't that often that we actually see the cleanings taking place at 00:00. I can update when we go live with the new formulas.

 

[Alex Blakenburg]

Thank you for the update, greatly appreciated.
I guess it depends a bit on what you are trying to measure.