Addition based on the column heading

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,454
Office Version
  1. 365
Platform
  1. Windows
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..

I am not good at excel. So, please help
 

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
Joined
Jun 24, 2016
Messages
26
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
Joined
Oct 2, 2008
Messages
1,454
Office Version
  1. 365
Platform
  1. Windows
Thanks NotC90. this works perfectly fine. Excellent job.
 

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,454
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 2, 2008
Messages
1,454
Office Version
  1. 365
Platform
  1. Windows
=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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Perhaps...

Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
I​
1​
BaseBaseBase
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
Joined
Oct 2, 2008
Messages
1,454
Office Version
  1. 365
Platform
  1. Windows
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:

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,454
Office Version
  1. 365
Platform
  1. Windows
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:

Aladin Akyurek

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

Forum statistics

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