Sum of Alternate Columns

[msiyab]

Hi Guys,

I would like to know a formula to add up alternate columns in excel.
I have attached the sample of the file I will be using.

Please note:
1) i need the totals of "Successful" columns in column J.
2) I need the totals of "Total" columns in column K.
3) I will need a self updating formula or something which wont need me to change the formula as and when i add columns after Column I. As this is a daily updated sheet, I will add two columns everyday and so the totals should updated automatically.

Please help.

1626

[Admin]

Hi

In J3

=SUMIF($D$2:I$2,"Successful",$D3:I3)

In K3

=SUMIF($D$2:J$2,"Total",$D3:J3)

I would suggest you to bring the Successful Total and Overall Total columns into Col D & E, so that the formula can be made dynamic.

In that case the formula would be

=SUMIF($F$2:AA$2,"Successful",$F3:AA3)

=SUMIF($F$2:AA$2,"Total",$F3:AA3)

Replace the highlighted AA with appropriate end column reference.

[LalitPandey87]

Hi

In J3 with Ctrl + Shift + Enter

=SUMIF(OFFSET($D$2,0,0,1,MAX(IF($D$2:$IV$2 = $D$2, COLUMN($D$2:$IV$2 ),0))-2),$D$2,OFFSET($D$2,ROWS($J$3:$J3),0,1,MAX(IF($D$2 :$IV$2 = $D$2, COLUMN($D$2:$IV$2 ),0))-2))

In K3 with Ctrl + Shift + Enter

=SUMIF(OFFSET($D$2,0,0,1,MAX(IF($D$2:$IV$2 = $E$2, COLUMN($D$2:$IV$2 ),0))-2),$E$2,OFFSET($D$2,ROWS($K$3:$K3),0,1,MAX(IF($D$2 :$IV$2 = $E$2, COLUMN($D$2:$IV$2 ),0))-2))

Tried with inserting new column in between and its working.

[msiyab]

Worked like a charm! Thank you


Hi

In J3 with Ctrl + Shift + Enter

=SUMIF(OFFSET($D$2,0,0,1,MAX(IF($D$2:$IV$2 = $D$2, COLUMN($D$2:$IV$2 ),0))-2),$D$2,OFFSET($D$2,ROWS($J$3:$J3),0,1,MAX(IF($D$2 :$IV$2 = $D$2, COLUMN($D$2:$IV$2 ),0))-2))

In K3 with Ctrl + Shift + Enter

=SUMIF(OFFSET($D$2,0,0,1,MAX(IF($D$2:$IV$2 = $E$2, COLUMN($D$2:$IV$2 ),0))-2),$E$2,OFFSET($D$2,ROWS($K$3:$K3),0,1,MAX(IF($D$2 :$IV$2 = $E$2, COLUMN($D$2:$IV$2 ),0))-2))

Tried with inserting new column in between and its working.

[Admin]

You may also try these non array fomulas as well.

In J3

=SUMIF($D$2:INDEX($D$2:IV$2,LastCol-2),"Successful",$D3:INDEX($D3:IV3,LastCol))

In K3

=SUMIF($D$2:INDEX($D$2:IV$2,LastCol-2),"Total",$D3:INDEX($D3:IV3,LastCol))

Hit Ctrl+F3 and define LastCol : =MATCH("zzzzzz",Sheet1!$D$1:$IV$1)