Basic Problem involving Sum

[Abhiroop]

Hi Guys,

I have a problem which seems rather easy, however I can't arrive at the solution.


Suppose you have two cols. Column A contains around 3000 names. And corresponding to every name, you have a certain value assigned which is in column B.

Now, I sort column B in descending order. I now want to add up the values in Col. B to figure out which rows make up 70% of the total value.

Suppose i see that the range (B2:B976) represents 70% of the total value.
Then I would like to add up this range (B2:B976) to find out the exact value.

The 70% value should be in a cell so that i can keep changing the number as per my convenience to probably 80% or 50% etc.

Is there a simple way to do this?

Thanks in advance!

 

[Bhawani]

Hi Abhi,

please find attached one give required % in cell c1.

 

[Abhiroop]

Hi Bhawani,

Thanks for your help! I am not able to understand the process.

PFA a file that i have uploaded showing an example of the problem with the steps involved. Please let me know if this would help?

Cheers!

 

[Swapnil]

Abhiroop ,

I have used simple conditional formatting & sum formula to derived at following sheet.

Request you to please see and suggest this meets your requirement or not.

 

[Bhawani]

PFA... If this match your requirement where
1. red highlighted cell is showing total of column B
2. yellow highlighted cell is showing % on which we will work on(getting range) 3. we can change % here from 70% to 80% or 50% or whatever you wish to.
skyblue highlighted cell will show the range for respictive required % range
4. Column D shows running total and will show all the row value for required %

In attached file minimum % value is taken.

Regards,

 

[Haseeb A]

Hello Abhi,

Here is one way:

=LOOKUP(70%,SUMIF(OFFSET(C3,,{1,0},ROW(D3:D3002)-ROW(D3)+1),"<>"))

Change 70% to your needs.

 

[Abhiroop]

Guys!

Thank you everybody for your awesome replies and all the help that you provided!

Haseeb, your formula really helped out and it solves the problem in a single step! Thanks a ton!

Swapnil and Bhawani, thanks a lot for your inputs! Really appreciate your help and multiple inputs and the time you took in trying to help me out!

Cheers!