#### snjpverma

##### Well-known Member
Excel Workbook
ABCDEFGHI
1BaseBaseBase
2837051735168849282
3678084836377508056
Sheet1

In the above data, A1, E1 and G1 is updated as "Base". Cell G1 is the left most column among them. Please suggest a formula to get the sum of the values in the row 2 and 3 from the left most column with text "Base". in the above case my return value will be +84+50 i.e. G2+G3 = 134..

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### NotC90

##### New Member
Hello,

this should work:
Code:
``=SUM(INDEX(\$A\$2:\$I\$3,,MATCH(2,1/(\$A\$1:\$I\$1="Base"))))``

This is an array-formula, so you have to enter it via Ctrl+Shift+Enter.

BR

#### snjpverma

##### Well-known Member
Thanks NotC90. this works perfectly fine. Excellent job.

#### snjpverma

##### Well-known Member
It would take me 100 years to derive at this formula. Amazing. I wish I knew how this works. Quite complicated.

#### snjpverma

##### Well-known Member
=SUM(INDEX(\$A\$2:\$I\$11,,MATCH(2,1/(\$A\$1:\$I\$1="Base"))))

I am using the above given array formula to add the values of the left-most column with word "Base" as a Header. The formula adds the whole column correctly. Can someone please help modify the formula so that it will add only the row 2, 3, 7, 8 and 10 ? Any help is highly appreciated.

##### MrExcel MVP
Perhaps...

 Row\Col A​ B​ C​ D​ E​ F​ G​ H​ I​ 1​ Base Base Base 2​ 83​ 70​ 51​ 73​ 51​ 68​ 84​ 92​ 82​ 3​ 67​ 80​ 84​ 83​ 63​ 77​ 50​ 80​ 56​ 4​ 150​ 114​ 134​ 5​ 6​ Total of 'Base' totals: 7​ 398​ 8​

In A4 enter and copy across:

=IF(A\$1="base",SUM(A2:A3),"")

In A7 control+shift+enter, not just enter:

=SUM(IF(\$A\$1:\$I\$1="Base",\$A\$2:\$I\$3))

#### snjpverma

##### Well-known Member
Excel Workbook
ABCDEFGHI
1BaseBaseBase
2837051735168849282
3678084836377508056
4559499185406248332274344410
5363207520303236371485547251
6425571570503525186335295324
7445105564304201576587189396
8266117192166277207118127138
9160162288138256184214187191
10405426467213268219337143446
114881625548617627431230524
Sheet1

thanks for your efforts Aladin. However, this is not what I wanted. I will try to explain again. My data looks as above wherein I have highlighted the rows that I want to be added i.e. 2, 3, 7, 8 and 10. In the above scenario my left-most column with header as "Base" is G1. hence the formula should add the values of G2, G3, G7, G8 AND G10.

Last edited:

##### MrExcel MVP
That sounds as a pretty arbitrary conditioning. Why rows 2, 3, 7, 8, and 10?

#### snjpverma

##### Well-known Member
These rows comes from a external document. Once, I get that total amount i.e. 1176 (84+50+587+118+337) in the above scenario. I will eventually have to compare that amount with some other cell.

Last edited:

##### MrExcel MVP
These rows comes from a external document. Once, I get that total amount i.e. 1176 (84+50+587+118+337) in the above scenario. I will eventually have to compare that amount with some other cell.

In A12 enter and copy across:

=IF(A\$1="base",SUMPRODUCT(--ISNUMBER(MATCH(ROW(A\$2:A\$11),{2;3;7;8;10},0)),A\$2:A\$11),"")

Replies
3
Views
285
Replies
7
Views
383
Replies
0
Views
607
Replies
3
Views
697
Replies
7
Views
1K

1,182,099
Messages
5,933,642
Members
436,902
Latest member
Ameratsu

### 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.

### Which adblocker are you using?

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

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