Hi,

In C12 you can enter the following formula:

=SUM(C2:C11)

Is that what you wanted?

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. 🙂

Hi Jessica 

i am using office 2019, thanks for your help, can u update the sumproduct formula based on code (A column) and name (B column) in cell c12

Thanks/Saliha

If I'm understanding you correctly it's just throwing an AND statement in there and updating the reference. IF(AND(SUMPRODUCT... Hope this helps! Please note you could also use COUNTIF the same way, but for some reason I really love SUMPRODUCT and that is usually my go-to.

Hi Jessica

I got my answer... thank you so much for giving precious time for me.

Thanks/Saliha

Hello Jessica 

Is there is any formula we can use in direct total cell & with out we can use Helper(=IF(AND(SUMPRODUCT(--($A$3:A10=A10))>1,SUMPRODUCT(--($B$3:B10=B10))>1),0,1))

Thanks/Saliha

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.

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