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

 

[snjpverma]

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

The above given formula calculates the specified rows from Column A. However, as highlihgted in Post #7, the formula should calculate the total from the column which has the left-most column as "Base". A1, E1 and G1 have the text "Base" in it but the left most is G1. Hence add the {2;3;7;8;10} of column G. Please refer the table pasted in post #7 and not post #1. Thanks for your efforts Aladin.

 

[bosco_yip]

Try,

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

Regards

 

[snjpverma]

Try,

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

Regards

Bosco_yip, Thanks a ton. This works perfectly as expected.

 

[Aladin Akyurek]

The above given formula calculates the specified rows from Column A. However, as highlihgted in Post #7, the formula should calculate the total from the column which has the left-most column as "Base". A1, E1 and G1 have the text "Base" in it but the left most is G1. Hence add the {2;3;7;8;10} of column G. Please refer the table pasted in post #7 and not post #1. Thanks for your efforts Aladin.

Efforts?...

Did you enter the formula:

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

in A2 and copied across? If you did, what was wrong?

 

[snjpverma]

Efforts?...

Did you enter the formula:

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

in A2 and copied across? If you did, what was wrong?

Aladin, i can surely use your formula in A11 to I11 which would be my helper row. But eventually I would need one more formula which would give me the real output.

Using Bosco's formula it goes like this.
K2 - Bosco's formula = Output

Since i am not that good at excel formulas, I don't know what the second formula would have been if I go by your method.

Nevertheless, I highly appreciate your support.

 

[Aladin Akyurek]

Aladin, i can surely use your formula in A11 to I11 which would be my helper row. But eventually I would need one more formula which would give me the real output.

Using Bosco's formula it goes like this.
K2 - Bosco's formula = Output

Since i am not that good at excel formulas, I don't know what the second formula would have been if I go by your method.

Nevertheless, I highly appreciate your support.

No need. I got it now.

 

[snjpverma]

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

 

[Aladin Akyurek]

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

is the expected value 2375 for the last you posted?

 

[snjpverma]

is the expected value 2375 for the last you posted?

Hi Aladin, My expected answer is 2270 i.e. sum of G2:G8 and G10. You seem to have added G3:G9 and G11.

 

[Aladin Akyurek]

Hi Aladin, My expected answer is 2270 i.e. sum of G2:G8 and G10. You seem to have added G3:G9 and G11.

Those numbers, 2 to 8, and 10 are thus native row numbers. If so...

In K1 just enter:

=LOOKUP(9.99999999999999E+307,SEARCH("base",A1:I1),COLUMN(A1:I1)-COLUMN(A1)+1)

For the sum, control+shift+enter, not just enter:

=SUMPRODUCT(INDEX(A2:I11,0,K1),IF(ISNUMBER(MATCH(ROW(A2:I11),{2;3;4;5;6;7;8;10},0)),1,0))