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>

 

[Comfy]

Tested up to row 300:

Excel 2010

	A	B
1	TOTAL SALES	73,811,241.00
2	80% of TOTAL SALES	59,048,992.80
3	NUMBER OF ACCOUNTS MAKING 80% OF TOTAL SALES	160
4
5
6	Account	2014 Net Sales
7	P2744002	498526
8	P1790073	496909
9	P1790068	496872

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Trade History​

....

Excel 2010

	A	B
291	P1790006	18904
292	P1790087	15861
293	P2676002	15412
294	P1790012	13809
295	P0973000	11745
296	P1790166	6389
297	P2676002	4460
298	P2568001	3696
299	P1790011	1343
300	P1790127	1030

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

Trade History​

Worksheet Formulas Cell Formula B1 =SUM(B7:B300) B2 =B1*0.8 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula B3 {=MATCH(MIN(ABS((MMULT(N(TRANSPOSE(ROW(B7:B300))<=ROW(B7:B300)),B7:B300)-B2))),ABS((MMULT(N(TRANSPOSE(ROW(B7:B300))<=ROW(B7:B300)),B7:B300)-B2)),0)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[p4nny]

Thanks for that... I'm running Excel 2007. I will try it on a different system with Excel 2010.

Appreciate your help.

 

[Aladin Akyurek]

Thanks for your reply.. I think minimum would be best.

Using the $650k as a target, cumulatively sum the values in each account until the $650k is achieved and return the number of accounts that make up the £650k.

Interesting that you still don't give that count (it must be something too obvious.).

Control+shift+enter, not just enter:

=MATCH(B2,SUBTOTAL(9,OFFSET(B7,0,0,ROW($B$7:$B$17)-ROW(B7)+1)),1)

If the count must satisfy the relation >= 650,047.42...

=MATCH(B2,SUBTOTAL(9,OFFSET(B7,0,0,ROW($B$7:$B$17)-ROW(B7)+1)),1)+
  (LOOKUP(B2,SUBTOTAL(9,OFFSET(B7,0,0,ROW($B$7:$B$17)-ROW(B7)+1))) < B2)

 

[p4nny]

Hi, you provided the above formula a while ago and it works fantastic..

For it to work, my data must already be ranked highest to lowest... would there a way incorporating that function into here?

 

[Aladin Akyurek]

Hi, you provided the above formula a while ago and it works fantastic..

For it to work, my data must already be ranked highest to lowest... would there a way incorporating that function into here?

Which formula are you addressing?

 

[p4nny]

Hi- this

=MATCH(B2,SUBTOTAL(9,OFFSET(B7,0,0,ROW($B$7:$B$17)-ROW(B7)+1)),1)+
(LOOKUP(B2,SUBTOTAL(9,OFFSET(B7,0,0,ROW($B$7:$B$17)-ROW(B7)+1))) < B2)

 

[p4nny]

Also, how would I add a criteria to this?

basically, I would like the formula to work only where column A (range) ="DK10" (criteria)

hope that makes sense

 

[Aladin Akyurek]

Hi- this

=MATCH(B2,SUBTOTAL(9,OFFSET(B7,0,0,ROW($B$7:$B$17)-ROW(B7)+1)),1)+
(LOOKUP(B2,SUBTOTAL(9,OFFSET(B7,0,0,ROW($B$7:$B$17)-ROW(B7)+1))) < B2)

This formula does not require any sorting involving B7:B17. Am I missing something?

 

[p4nny]

For the formula to work the data has to already be in ascending order as it counts how many customers make up a target figure.. if the data is not sorted then it will the complete wrong answer.. Would it be possible to add the above criteria?

 

[Aladin Akyurek]

For the formula to work the data has to already be in ascending order as it counts how many customers make up a target figure.. if the data is not sorted then it will the complete wrong answer.. Would it be possible to add the above criteria?

I don't understand why you insist on sorting the data. Is the sample in post #1 representative of your data? The figures appear in descending order: Is this done by you or is this how the data comes in?

You could post representative data as it occurs and state the outcome you want to see, related that sample.