Excel formula to count and sum

[Ironman]

Hello

I'm working with an excel file that starts off with two columns and 1300 rows. Employee ID in A and Salary dollars earned in Column B. The total in column B is, let say $60,000,000.00. This is sorted by column B, desending. I've added a third column that tells me what 5% of the $60mil is (c2), C3 = what 10% of 60mil is, C4 = What 15% is, C5 = What 20% of 60mil is...and so on to what 95% of 60mil is in C20.

What I'm trying to determine is how many employees take up or make up 5% of the total salary (60mil)?, then how many employees take up 10% of the 60 mil, and so on to 95% of the 60mil.

I'm not sure if this is a countif or which direction I should go...any thoughts/suggestions would be great...thanks!!!

[NBVC]

Maybe:

=Countif(B:B,"<="&C2) copied down.

[Ironman]

Thanks for you reply. I've attached an example of dummy data. I put your suggested formula in column D and copied down. As you see the results show 99 for all but the first cell. (the data only has 100 rows of data)....

Any other suggestions?

[NBVC]

Maybe I don't follow...

What do you expect to see in D2, D3, and D4 for example?

[Ironman]

NBVC - What I'm hoping to see in column D would be something like:

D2 - 2 employee's salaries make up 5% of the total salary, D3 - 8 employees make up of 10% of the salary, D4 - 23 employees make up 15% of the total salary, D5 - 32 make up 20% of the total salary...on upto 98 employees make up 95% of the total salary...does that make senese. I think the countif is on the right track...maybe a countif(and(....?

[NBVC]

I am not getting it...maybe too long a day... I had to pick up my kid today from daycare because he was running a fever... so got back just now.... Can you do me a favour and spell it out... how do you come up with 2, 8, 23, 32, etc..? It's not kicking in for some reason.

[Ironman]

NBVC - - I do hope your sick child is OK...that is far more important than anything I need, that is for sure! I've been asked to 'update' an older report. The numbers I provided earlier (8, 23, 32..etc) were just off the top of my head, made up numbers. This report was in Word (I'll try to attach it), this doc had a table which looks like this (in case the attachment, doesn't attach):

Please Login or Register  to view this content.

The explanation was stated as follows:

Please Login or Register  to view this content.

I've put the above in code brackets...Let me know if this make more sense.

[NBVC]

Try this formula in D2 of your sample:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and not just ENTER.. you will see { } brackets appear...

Then copy it down the list.

If you make any changes, you need to reconfirm with those CSE keys before copying down again.

Does this work for you?

[Ironman]

Good morning...I do hope that your child is doing better today. I tried your suggestion and I do get a return that seems OK. However, I don't know what exactly the formula is doing. Could you explain that out some? For example, using the formula on the test file I attached earlier I get 3 in cell D2, which means the top 3 employees make up 5% of the total compensation. The example shows the top 3 employees making: $607k, $533k and $499k. The 5% number of total compensation is $794k. In this example should D2 read 1 verses 3?

Thanks again for your help on this....

[NBVC]

Hi,

Thanks for your concern, my son still has fever, but looking better... he is at grandma's today... who will spoil him... I am sure ... those daycares are notorious to pass around bugs... he gets something every so often....but fortunately he fights them off in short period of time.

On to your dilemma... does this one make more sense?

Please Login or Register  to view this content.

confirmed with CSE keys and copied down.

By the way, your first 3 values in the attached were not $607k, $533k and $499k they were, they were 1,607k, 1,133k and 499k...

What it does is creates a range of rolling sums for column A, then compares number in column C to this range and looks for the first time the rolling sum is greater than or equal to the value in C... this gives the position of that TRUE result.. but we have to subtract 1 to tell us the last time that the rolling sum is less than that value in C up to that first TRUE result....

I hope that makes some sense.

[Ironman]

Hi - - Yes, my daughter is the same, doesn't get sick too often and when she does she seems to bounce back quickly.

I tried your updated formula and it seems to be much better. I kind of know what is going on there, but it is still a little fuzzy for me, none the less. As far as the numbers I provided, sorry about that, I've sliced and diced the example/dummy data a couple of times so I'm sure I've mixed a couple up...thanks again, I think we good to go. Have a good day and best wishes to you and your family.

[NBVC]

Not sure if you caught my last edit in my previous post, where I tried to explain the formula...

I am glad it seems to work to your satisfaction. Have a great day

Please remember to mark the thread as Solved:


How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save