Excel：按表格条件提取对应设施的最新最大日期
Hey there! Let's tackle this problem—since MAXIFS isn't working out for you, let's break down alternative approaches that fit your unique table structure.
First, Let's Fix MAXIFS (If You Just Need a Small Tweak)
If you're using Excel 2019, 365, or later, MAXIFS should work, but tiny details can trip it up:
- Match your formula to your column layout exactly. For example, if:
- Column A = Facility IDs (like 200)
- Column B = Condition (Major/Minor)
- Column C = Dates
The correct formula for Facility 200 would be:=MAXIFS(C:C, A:A, 200, B:B, "Major")
- Double-check for typos: Ensure "Major" matches your table exactly (no extra spaces—capitalization doesn't matter here, but stray spaces will break the match).
- Confirm your dates are actual date values, not text. If they're text, convert them first with
DATEVALUE()or the "Text to Columns" tool.
If MAXIFS Still Fails (Due to Unique Table Structure)
If your table is laid out differently than typical examples (e.g., facilities as column headers, dates in rows), try these workarounds:
Example 1: Facilities as Column Headers
Suppose your table has facilities in row 1, dates in column A, and conditions in column B. To get the latest date for Facility 200 where the condition is "Major", use this array formula (press Ctrl+Shift+Enter for older Excel; modern Excel just needs Enter):=MAX(IF((B:B="Major")*(C:C<>"")*(C:C=TRUE), A:A))
(Adjust C:C to match the column for Facility 200)
Example 2: Older Excel Versions (No MAXIFS Support)
If you're on Excel 2016 or earlier, use SUMPRODUCT to mimic MAXIFS:=SUMPRODUCT(MAX((A:A=200)*(B:B="Major")*C:C))
Swap A:A, B:B, and C:C to match your facility ID column, condition column, and date column.
Testing with Your Example
For Facility 200 targeting 7/7/2014, plug your correct column references into one of these formulas. If it still doesn't work, double-check that the row with 7/7/2014 is indeed marked as "Major" for Facility 200—small data entry errors are often the culprit!
内容的提问来源于stack exchange，提问作者sdb0020
