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

 

[bosco_yip]

I used this {1,2,6,7,9} in the below formula to sum the value in those rows.
=SUM(N(OFFSET(INDEX(A1:I1,0,MATCH(2,INDEX(1/(A1:I1="Base"),0))),{1,2,6,7,9},0)))

I would like to know if there is a way to sum a range using this? For example. Instead of rows 1,2,6,7,9 how can I put rows 2 to 8 and 10. I used {2:8, 10} in the above formula but in vain. Please advise

Try,

=SUM(N(OFFSET(INDEX(A1:I1,0,MATCH(2,INDEX(1/(A1:I1="Base"),0))),{1,2,3,4,5,6,7,9},0)))

Regards
Bosco

 

[snjpverma]

Works great. But I don't know what is the role of N there? I tried using evaluate formula but still could not get it. Please explain me the role of N in the above formula. Thanks in advance