英文:
MAX time with IF NOT OR function
问题
你的公式有一个问题，它没有正确地排除列D中值为"SYSTEM"或"VOID"的行。正确的公式应该是：
=MAX(IF((D3:D13<>"SYSTEM")*(D3:D13<>"VOID"), B3:B13))
请尝试使用这个公式，它应该能够返回你期望的结果，即"7:08:23 AM"。
英文:
My data as per below.
I am trying the find the latest time in column B, given column D criteria whereby it is not SYSTEM OR VOID.
| Staff ID | Time | Date | Staff |
|---|---|---|---|
| 34565 | 9:00:35 AM | 02/05/2023 | SYSTEM |
| 23586 | 5:32:05 AM | 05/05/2023 | SYSTEM |
| 46354 | 4:15:35 AM | 05/05/2023 | ALEX |
| 46546 | 4:09:30 AM | 06/05/2023 | CLARE |
| 98744 | 2:54:18 AM | 12/05/2023 | JOHN |
| 34534 | 3:23:10 AM | 20/05/2023 | SANDY |
| 87675 | 4:32:09 AM | 21/05/2023 | MANDA |
| 35645 | 7:15:23 AM | 26/05/2023 | VOID |
| 23423 | 6:15:23 AM | 08/05/2023 | ALEX |
| 23423 | 3:46:15 AM | 09/05/2023 | KEN |
| 34564 | 7:08:23 AM | 08/05/2023 | KEAT |
I tried to use the formula: =MAX(IF(NOT(OR(D3:D13="SYSTEM",D3:D13="VOID")),B3:B13))
however it returns 12:00:00 AM which is not the desired result, 7:08:23 AM.
What is wrong with my formula?
答案1
得分: 1
=MAXIFS(B2:B12, D2:D12, "<>SYSTEM", D2:D12, "<>VOID")
英文:
In EXCEL-2021 should have MAXIFS() function. Try-
=MAXIFS(B2:B12,D2:D12,"<>SYSTEM",D2:D12,"<>VOID")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论