suming every 3rd column

[Steved]

Hello from Steved

Looking at the below I hide 2 Colums for example K and L Columns and so on

Is there please a Sum function I can use to sum colums but ignore the hidden
columns.

=J3+M3+P3+S3+V3+Y3+AB3+AE3+AH3+AK3+AN3+AQ3

Thankyou.

 

[Gaurav]

=SUBTOTAL(109,J3:AQ3)

 

[Pete_UK]

The 109 only works for hidden rows, not hidden columns unfortunately.

Pete

 

[Steved]

Hello Gaurav

You are excellent Value I thankyou.

 

[Martin Fishlock]

Hi Steve D

Try using this formula

=SUMPRODUCT(--(MOD(COLUMN(J1:AS1),3)=1),(J1:AS1))

What it does is checks every third row using the column number and getting
the modulus of it of 3 (ie giving 0,1,2) and only selects the cells where the
mod = 1.

MOD(COLUMN(J1:AS1),3)=1<------

You need to modify the last one in the above if you move cells.

=SUMPRODUCT(--(MOD(COLUMN(O1:AX1),3)=MOD(COLUMN($O$1),3)),(O1:AX1))

Or you can add an extra starting cell to work out which is the mod to use.

 

[Martin Fishlock]

Whoops,

checks every third row using

should be

checks every third column using

 

[Steved]

Hello Martin from Steved

Thanks

Hi Steve D

Try using this formula

=SUMPRODUCT(--(MOD(COLUMN(J1:AS1),3)=1),(J1:AS1))

What it does is checks every third row using the column number and getting
the modulus of it of 3 (ie giving 0,1,2) and only selects the cells where the
mod = 1.

MOD(COLUMN(J1:AS1),3)=1<------

You need to modify the last one in the above if you move cells.

=SUMPRODUCT(--(MOD(COLUMN(O1:AX1),3)=MOD(COLUMN($O$1),3)),(O1:AX1))

Or you can add an extra starting cell to work out which is the mod to use.

 

[Steved]

Hello Pete from Steved

Thankyou

The 109 only works for hidden rows, not hidden columns unfortunately.

Pete