Search code examples
excelexcel-formulacountifsumifs

Sum all, ignoring first instance of value

Skip first instance of sumifs criteria

I am having trouble creating a formula to sum all the values in column B, to ignore the first instance of the items in column A. Desired output can be seen in column D and E.

Solution

  • Here is one way of doing this:

    enter image description here

    =SUM(B$2:B$18*(MAP(A$2:A$18,LAMBDA(x, COUNTIF(x:$A$2,x)))>1)*(D2=A$2:A$18))

    Or better use this formula to spill down:

    enter image description here

    =LET(α, A2:A18, MAP(D2:D4,LAMBDA(x, SUM(B2:B18*(α=DROP(VSTACK("",α),-1))*(x=α)))))

    Or Combined output using one single dynamic array:

    enter image description here

    =LET(
     _Data, A2:B18,
     _Item, TAKE(_Data,,1),
     _UniqItem, UNIQUE(_Item),
     _Hours, MAP(_UniqItem, LAMBDA(α, SUM(TAKE(_Data,,-1)*(DROP(VSTACK("",_Item),-1)=_Item)*(_Item=α)))),
     HSTACK(_UniqItem, _Hours))