

Trusted Members
Moderators
Power BI

January 31, 2022



Trusted Members

February 13, 2021

What version of Excel are you on? If you have 365 you can use a combination of sum/unique. Unfortunately, I don't have the latest version so I'm not sure what that would look like. Otherwise I've concocted a SUMPRODUCT formula that works. You would need a helper column, with the formula =IF(SUMPRODUCT(--($B$3:B3=B3))>1,0,1), this is to get to get the unique names. Then a simple SUMIF for the total.
=SUMIF(D3:D11,1,C3:C11)
Hope this helps. 🙂


Trusted Members

February 13, 2021



Trusted Members

February 13, 2021

That was a fun challenge! Thank you for the opportunity to learn how a new function works!
I was able to figure out a formula using sumproduct(frequency... Please note: I'm not sure why the frequency formula evaluates one row extra so I compensated by extending the rows added by one (in red below) and then subtracting that row back out assuming there is data you don't want to include in that row. Hopefully someone who knows the function better than me can tell us why it evaluates 9 rows when there are only 8 selected.
Either way here is what I concocted:
=SUMPRODUCT(((FREQUENCY(IF(A3:A10<>"",MATCH(A3:A10,A3:A10,0)),ROW(A3:A10)-ROW(A3)+1)>=1)*$C$3:$C$11)-C11)
This is an array formula so you will need to enter using ctrl+shift+enter.

VIP

Trusted Members

December 7, 2016

Hello,
Another almost identical formula where we skip the frequency part is something like this.
=SUM(IF(MATCH($A$3:$A$10,$A$3:$A$10,0)=ROW($A$3:$A$10)-2,$C$3:$C$10))
And if you want to make sure you only get the salary for persons having a code, do as Jessica did.
=SUM(IF($A$3:$A$10<>"",IF(MATCH($A$3:$A$10,$A$3:$A$10,0)=ROW($A$3:$A$10)-2,$C$3:$C$10)))
If there is a risk that the codes are reused for other persons, then it will complicate things. As it is now I assume the codes are unique, so there should be no need to check for the names also.
Br,
Anders
1 Guest(s)
