Search code examples
excelexcel-formulaexcel-2019

How do I create a column that will return sum of another column, based on the maximum value of only certain groups of cells?

I'm using Excel 2019.

enter image description here

I need to see this data by the ID group.

If the status of the maximum value "not ok", then the result column won't return anything. Example, ID "456". For this ID, the maximum value is 4 (row 8) and the status of that value is "not ok". So the result is 0.

For ID "123", the maximum value is 5 (row 5) and the status of that maximum value is "ok". So the result column will return the sum of "sum" column for that ID:

= row 1 + row 2 row 4 + row 5 + row 9

= 10 + 20 + 40 + 50 + 30

= 150

That result can be shown either on the smallest value row (row 1) or largest value row (row 5), but can not be on every row.

Solution

  • Posted the answer in other query of OP, posting here as well, though there is no huge difference between last post and the newer one, only a minor.

    enter image description here

    =IF((MAX((B2=$B$2:$B$10)*$D$2:$D$10)=D2)*(E2="ok"),SUM($D$2:$D$10*($B$2:$B$10=B2)),0)

    Or,

    =IF((MAXIFS($D$2:$D$10,$B$2:$B$10,B2)=D2)*(E2="ok"),SUM($D$2:$D$10*($B$2:$B$10=B2)),0)