| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| testing while suming | New Users to Excel | |||
| suming in add-ins | Excel Worksheet Functions | |||
| Suming values from a xls file | Excel Worksheet Functions | |||
| Suming up a Lookup | Excel Worksheet Functions | |||
| Suming 2 cells if 1 = #N/A | Excel Worksheet Functions | |||
Linear Mode
