HI All
I am trying to edit part of a formula based on the value of another cell (sample sheet attached).
The number in cell H2 refers to the vlookup on sheet 2 which returns a result in cell I2.
This result from cell I2 should be put into the formula in cell I12 between { }
Basically what I am trying to achieve is to be able to change the number of smallest values sums returned based on the number of dropped rounds.
Thanks
Try this in I12.
=SUM(SMALL(A12:G12,TEXTSPLIT(I1,",")))
Try this:
=SUMPRODUCT((A12:G12<=SMALL(A12:G12,H1))*(A12:G12))
or:
=SUMPRODUCT(SMALL((A12,B12,C12,D12,E12,F12,G12),SEQUENCE(,H1,,1)))
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.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I should have mentioned that in the actual sheet the A12:G12 are not contigous however i adjusted the formula for this and it still works so thanks very much.
Edit - forgot to quote your post windknife. Worked great , thanks
Last edited by nathp; 12-12-2023 at 12:25 PM. Reason: Missed quote
@AliGW
This also worked perfectly thanks
Last edited by AliGW; 12-12-2023 at 12:29 PM. Reason: Please do NOT quote unnecessarily! Use the QUICK REPLY button instead.
Thanks for the rep.
You are welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks