Log In
Register
Home
December 8, 2017
Offline
Hi Every one
I have stuck in total in salary C12 cell, any one please help to update the formula for based on that code & name
Thanks & Regards / Madi
Trusted Members
Moderators
January 31, 2022
Offline
Hi,
In C12 you can enter the following formula:
=SUM(C2:C11)
Is that what you wanted?
Trusted Members
February 13, 2021
Offline
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. 🙂
December 8, 2017
Offline
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
Trusted Members
February 13, 2021
Offline
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.
February 2, 2013
Offline
Hi Jessica
I got my answer... thank you so much for giving precious time for me.
Thanks/Saliha
February 2, 2013
Offline
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
Trusted Members
February 13, 2021
Offline
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
Offline
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)
