As OriginalGriff mentioned, there's something wrong with your data... Neverthless...
MAXIFS
in Excel corresponds with
MAX()
window function in SQL. See:
CREATE TABLE Test
(
ABC varchar(30),
Level int,
IsValid varchar(30)
);
INSERT INTO Test(ABC, Level, IsValid)
VALUES ('10D34AV', 0, 'No'),
('10D34AV', 1, 'No'),
('10D35AV', 0, 'No'),
('10D35AV', 1, 'No'),
('10D36AV', 0, 'Yes'),
('10D36AV', 1, 'Yes'),
('10D36AV', 2, 'Yes'),
('10D36AV', 2, 'Yes'),
('10D36AV', 2, 'Yes'),
('10D36AV', 1, 'Yes'),
('10D36AV', 2, 'Yes'),
('10D37AV', 0, 'Yes'),
('10D37AV', 1, 'Yes'),
('10D37AV', 2, 'Yes'),
('10D37AV', 2, 'Yes'),
('10D37AV', 1, 'Yes'),
('10D37AV', 2, 'Yes'),
('10D38AV', 0, 'Yes'),
('10D38AV', 1, 'Yes'),
('10D38AV', 2, 'Yes'),
('10D38AV', 2, 'Yes'),
('10D38AV', 2, 'Yes'),
('10D38AV', 1, 'Yes'),
('10D38AV', 2, 'Yes'),
('10D39AV', 0, 'No'),
('10D39AV', 1, 'No');
SELECT ABC, Level, IsValid, MAX(Level) OVER(PARTITION BY ABC ORDER BY ABC) MaxOfLevel
FROM Test
Result:
SQL Server 2019 | db<>fiddle[
^]