count with the number of cells by that equal a target by summing the range.

[p4nny]

Hi,

In my example, I'm trying to create a formula that would return the "numbers of customers making up 80% of revenue".

I want to sum accounts cumulatively until the target is reached "80%") (or the closest match) and count how many accounts this is.

Many thanks for your help, Anthony

Column A Column B

TOTAL SALES	812,559.27
80% of TOTAL SALES	650,047.42
NUMBER OF ACCOUNTS MAKING 80% OF TOTAL SALES	????
Account	2014 Net Sales
P2063001	292,575.82
P2568001	110,493.34
P1790003	100,088.84
P1790004	94,744.40
P1126001	86,057.82
P2744002	53,009.23
P1790006	44,677.92
P0973000	18,881.50
P1790008	11,578.72
P2676002	610.08
P1790000	- 158.40

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[p4nny]

sorry yes, descending.
The example in post 1 is an exact presentation of what I'm trying to do.. However I have to manually sort the data in this way.

If I don't have the data descending, the formula will not give the desired result as the idea is that it returns a count based on the highest values.

Additionally, would it possible to add criteria to your formula? column A (range) ="DK10" (criteria)

 

[Aladin Akyurek]

sorry yes, descending.
The example in post 1 is an exact presentation of what I'm trying to do.. However I have to manually sort the data in this way.

If I don't have the data descending, the formula will not give the desired result as the idea is that it returns a count based on the highest values.

Additionally, would it possible to add criteria to your formula? column A (range) ="DK10" (criteria)

Would you please post the sample as it occurs, unsorted?

 

[p4nny]

please see below... The 443,905 is the target, in column B,your formula returns a count of how many cells make up the target. For it work effectively the Profit column must be in descending order. Underlined is the question that is answered from your formula but only if column B is in descending order.

column A contains country, additionally i would like to be able to select a country based on criteria within the formula. = DK10 for example or SE10.

hope this makes sense. appreciate your time!

How many customers generate 80% of the profit???
80% of profit = 443,905
TOTAL PROFIT	554,881
Country	Profit
DK10	240,099
DK10	99,923
DK10	25,702
DK10	3,888
DK10	39,746
FI10	19,116
FI10	2,057
FI10	1,931
FI10	28,575
FI10	4,020
FI10	4,583
NO10	1,570
NO10	239
NO10	119
NO10	4,122
NO10	2,031
SE10	17,316
SE10	28,575
SE10	3,216
SE10	22,860
SE10	2,512
SE10	2,680

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

please see below... The 443,905 is the target, in column B,your formula returns a count of how many cells make up the target.

Correct. It does that effectively.

For it work effectively the Profit column must be in descending order. Underlined is the question that is answered from your formula but only if column B is in descending order.

If the question is "What is N of the N largest profit records that satisfy 80% of the total profit?", then you are right. We need the profit records in descending order if we want to invoke the formula for this very question.

What should the formula(s) return as answer to this question with respect to the sample you provide?

column A contains country, additionally i would like to be able to select a country based on criteria within the formula. = DK10 for example or SE10.
[...]

Again, what should the formula(s) return as answer when we select DK10 as criterion/condition with respect to the sample you provide?