suming every 3rd column
 

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.

suming every 3rd column
 

=SUBTOTAL(109,J3:AQ3)

"Steved" wrote in message
...
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.

suming every 3rd column
 

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

Pete

"Gaurav" wrote in message
...
=SUBTOTAL(109,J3:AQ3)

"Steved" wrote in message
...
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.

suming every 3rd column
 

Hello Gaurav

You are excellent Value I thankyou.

"Gaurav" wrote:

=SUBTOTAL(109,J3:AQ3)

"Steved" wrote in message
...
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.

suming every 3rd column
 

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:AX 1))

Or you can add an extra starting cell to work out which is the mod to use.
--
Hope this helps
Martin Fishlock, NYF Consultants, Bangkok, Thailand
Please do not forget to rate this reply.


"Steved" wrote:

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.

suming every 3rd column
 

Whoops,

checks every third row using

should be

checks every third column using
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Martin Fishlock" wrote:

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:AX 1))

Or you can add an extra starting cell to work out which is the mod to use.
--
Hope this helps
Martin Fishlock, NYF Consultants, Bangkok, Thailand
Please do not forget to rate this reply.


"Steved" wrote:

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.

suming every 3rd column
 

Hello Martin from Steved

Thanks

"Martin Fishlock" wrote:

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:AX 1))

Or you can add an extra starting cell to work out which is the mod to use.
--
Hope this helps
Martin Fishlock, NYF Consultants, Bangkok, Thailand
Please do not forget to rate this reply.


"Steved" wrote:

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.

suming every 3rd column
 

Hello Pete from Steved

Thankyou

"Pete_UK" wrote:

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

Pete

"Gaurav" wrote in message
...
=SUBTOTAL(109,J3:AQ3)

"Steved" wrote in message
...
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.