• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

HELP: Advanced sum formula

M

Moto0911

New Member
Hello all,

I need help with a SUM Formula that can SUM until a specific amount is reached and then take the highest amounts to reach it.

Can excel do that?

Thanks.
 
Try,

In B8, enter array formula (confirmed by "Ctrl"+"Shift"+"Enter" to enter it) :

=SUM(C4:INDEX(4:4,MATCH(-B7,-SUMIF(OFFSET(B4,,,,COLUMN(A1:CH1)),"<>")+1)))

73808
 
The formula is supposed to also take the amounts in account. So it has to take the the highest cells to reach B7, i did it manually, and then it takes 18 of the highest cells to reach B7. :)
 
Last edited:
Moto0911
Did You notice ... I did two questions?
For me ... positive value is higher than negative ... for some reason.
If positive values are in the top of list and negative in the end of that list then ... is Your logic same?
Without Your answers as well as with clear logic - others could only guess.
 
Sorry, I didn't realize that :)

- Yes, but it only have to sum the negative ones, so the -2075244 is the highest.

- No we are only interested in the negative ones.

Please bear with me, i'm not good at excel terms in english. :)
 
Moto0911
You could get something like this ...
... but this sample could use as well as positive values.
 

Attachments

  • Help.xlsx
    13.9 KB · Views: 3
You must log in or register to reply here.
Back
Top