stuck with SUM

shkhaslam

New Member
Joined
Nov 22, 2014
Messages
44
25%
12000
15%21200
4%3320
4%4320
4%5320
4%6320
4%7320
3%8240
3%9240
3%10240
3%11240
3%12240
3%13240
3%14240
3%15240
3%16240
3%17240
3%18240
2%19160
2%20160
2%21160
1%2280

<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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

shkhaslam

New Member
Joined
Nov 22, 2014
Messages
44

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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:
Rich (BB code):

=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

New Member
Joined
Nov 22, 2014
Messages
44

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
Nov 22, 2014
Messages
44
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 ..
 

Watch MrExcel Video

Forum statistics

Threads
1,122,187
Messages
5,594,748
Members
413,930
Latest member
Nela817

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top