Addition based on the column heading

[snjpverma]

Excel Workbook
	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
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

 

[NotC90]

Hello,

this should work:

=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]

Thanks NotC90. this works perfectly fine. Excellent job.

 

[snjpverma]

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

 

[snjpverma]

=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]

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]

Excel Workbook
	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	559	499	185	406	248	332	274	344	410
5	363	207	520	303	236	371	485	547	251
6	425	571	570	503	525	186	335	295	324
7	445	105	564	304	201	576	587	189	396
8	266	117	192	166	277	207	118	127	138
9	160	162	288	138	256	184	214	187	191
10	405	426	467	213	268	219	337	143	446
11	488	162	55	486	176	274	312	305	24
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.

 

[Aladin Akyurek]

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

 

[snjpverma]

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.

 

[Aladin Akyurek]

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),"")