stuck with SUM

[shkhaslam]

25%	1	2000
15%	2	1200
4%	3	320
4%	4	320
4%	5	320
4%	6	320
4%	7	320
3%	8	240
3%	9	240
3%	10	240
3%	11	240
3%	12	240
3%	13	240
3%	14	240
3%	15	240
3%	16	240
3%	17	240
3%	18	240
2%	19	160
2%	20	160
2%	21	160
1%	22	80

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

criteria : 78%
First I want Excel To check where 78% would be from column A (ie : num 13 ) and then excel should sum from 1-13 from column c and give the desired result

 

[Aladin Akyurek]

Why/how is 'num 13' 78%?

 

[shkhaslam]

Why/how is 'num 13' 78%?

If you calculate from column B from 1 to 13 the sum of percentage in column A is 78%

 

[Aladin Akyurek]

If you calculate from column B from 1 to 13 the sum of percentage in column A is 78%

The desired output then would be 240. What if we have to test for 61%?

 

[shkhaslam]

The desired output then would be 240. What if we have to test for 61%?

The desired output should be sum of 1 to 13 from column C which is 6240/-

There's no 61% .. If criteria is 61% then there should be error ...
It should calculate row+row & check if criteria meets..

 

[Aladin Akyurek]

The desired output should be sum of 1 to 13 from column C which is 6240/-

There's no 61% .. If criteria is 61% then there should be error ...
It should calculate row+row & check if criteria meets..

Row\Col	A​	B​	C​	D​	E​
1​	25%​	1​	2000​		78%​
2​	15%​	2​	1200​		6240​
3​	4%​	3​	320​
4​	4%​	4​	320​
5​	4%​	5​	320​
6​	4%​	6​	320​
7​	4%​	7​	320​
8​	3%​	8​	240​
9​	3%​	9​	240​
10​	3%​	10​	240​
11​	3%​	11​	240​
12​	3%​	12​	240​
13​	3%​	13​	240​
14​	3%​	14​	240​
15​	3%​	15​	240​
16​	3%​	16​	240​
17​	3%​	17​	240​
18​	3%​	18​	240​
19​	2%​	19​	160​
20​	2%​	20​	160​
21​	2%​	21​	160​
22​	1%​	22​	80​

E2, control+shift+enter, not just enter:

=SUM(C1:INDEX($C$1:$C$22,MATCH(9.99999999999999E+307,
   1/(SUBTOTAL(9,OFFSET(A1,0,0,ROW($A$1:$A$22)-ROW($A$1)+1))=E1))))

 

[shkhaslam]

It's Perfectly Working Sir .. thanks a lots

But I did not understand the formula can you expalin me please is 9.99999999e & what is subtotal & what is offset ? And how is it matching

Thanks a lots for your help

 

[Aladin Akyurek]

It's Perfectly Working Sir .. thanks a lots

You are welcome.

But I did not understand the formula can you expalin me please is 9.99999999e & what is subtotal & what is offset ? And how is it matching

Thanks a lots for your help

01. It's 9.99999999999999E+307... See: http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html.

02. SUBTOTAL running 9 sums the range it's given.

03. OFFSET is set up such that it creates the following ranges: A1:A1, A1:A2, A1:A3, etc. each of which SUBTOTAL sums.

04. The MATCH bit finds row number at which the subtotals equals E1 and hands over that row number to INDEX.

05. SUM sums C1 up to the cell address MATCH has calculated.

Hope this terse helps a bit.

 

[shkhaslam]

Yes,Understood Correctly ... Thanks alladin for your help ... I have few more queries in excel .. If you can help me i'll PM you the query ..