Hi everyone, this is my first time posting and I feel kinda dumb for asking you guys for this but I just can't figure out.
I want to sum a row with diferent values, for example from 1 to 10. But I want to give this numbers (1,2,3,4,etc) a different value that the one that is displaying when they are summed. For example, If the cell have a number 1 I want to make it have a value of 10, 2 = 9, 3=8, and so on.
I was trying with SUMIF(s) but I just can't. Do you have some advice?
Thanks!
Welcome to the forum!
I think this would work:
=SUM(LOOKUP(B$3:B$7,{1,2,3,4},{10,9,8,7}))
where B$3:B$7 is your sum range.
Last edited by AliGW; 03-07-2019 at 02:19 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Hi Ali! Thank you for your welcome to the forum and also your quick response!
I tried what you said and I got an #VALUE! error.
What am I doing wrong?
Error.PNG
I don't know. Attach the workbook, not a picture of it.
You could try this:
=SUM(VALUE(LOOKUP(B$4:E$4,{1,2,3,4},{10,9,8,7})))
After entering formula by pressing ctrl+shift+enter
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
After pressing ctrl+shift+enter it worked! thank you both so much for your time!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
They work without CSE here, but that may be because I am on the Office insider programme and already have the new dynamic formula functionality.
That formula is too error prone to be considered useful.
The empty cell in E2 will generate a #N/A error, this will be shown as the result of the formula over the sum of the valid results.
The formula is only using approximate matching so using the example given any number greater than 4 will be evaluated as 4 instead of being ignored / valued as zero.
The more reliable method, using a table to hold the values instead of listing them in the formula:-
- In N2:N5, enter a list of original values, i.e. 1,2,3,4.
- In O2:O5, enter the list of equivalent values, i.e. 10,9,8,7.
- In G2, enter the formula
Formula:
Please Login or Register to view this content.
Sumproduct creates the array, so you don't need CSE. This will also eliminate any potential errors that the lookup method would create.
The lookup formula could be modifed to work without errors, but that would just be unnecessary over-complication of a fairly simple task.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks