+ Reply to Thread
Results 1 to 6 of 6

Sum every 4th column

  1. #1
    Registered User
    Join Date
    06-26-2015
    Location
    London, England
    MS-Off Ver
    2007
    Posts
    3

    Sum every 4th column

    Hi,

    I have been creating a work time sheet for myself (its pay day today and I think I've been underpaid).

    I need a formula that can calculate a sum from every 4th column.

    Starting from B3, I would like it calculate B3, F3, J3, N3 etc and add them up.

    Please help. My result would go in A1.

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sum every 4th column

    Hi.

    Please re-read the forum rules, particularly that one relating to cross-posting:

    http://www.mrexcel.com/forum/excel-q...th-column.html

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Sum every 4th column

    This array formula will do the job for you (goes out to column ZZ - adjust to go further, if needed):

    =SUM(IF(MOD(COLUMN($B$3:$ZZ$3)-2,4)=0,B3:$ZZ$3,0))

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly braces yourself - it won't work...
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sum every 4th column

    Quote Originally Posted by Glenn Kennedy View Post
    =SUM(IF(MOD(COLUMN($B$3:$ZZ$3)-2,4)=0,B3:$ZZ$3,0))
    It's even better if you replace the constant subtractor with a dynamic clause.

    For example, if the OP one day decides to move their data from B3:ZZ3 to, say, A3:ZY3, this formula will no longer give correct results. But will the OP necessarily know what needs changing in that formula?

    Also, perhaps it could be said that we should only use an array formula when it is not possible to do without. Here, SUMPRODUCT is perfectly valid:

    =SUMPRODUCT(0+(MOD(COLUMN(A3:ZY3)-MIN(COLUMN(A3:ZY3)),4)=0),A3:ZY3)

    and also now, no matter to what the range is changed, this set-up will always give correct results, without the need to manually redetermine what the constant (-2 in your case) should become.

    Regards

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Sum every 4th column

    XORLX, thanks for that. A comment and a Q. I accept fully that the non-array alternative is better, and that in this case, the use of a dynamic clause is preferable. In this case, the data to be summed started at B3 - there could have been no other interfering data to the left of B3. However, if the cells to be summed had started at, say, H3, there would have been a considerable potential for incorrect answers if a number was present in D3. How woudl you have adapted this to cope with that possibility?

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sum every 4th column

    @Glenn

    Not sure I follow. You mean apart from simply starting the referenced range at H3?

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Compare multiple columns (column A,column B,Column C) and return value (Column D)
    By john008 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-21-2015, 08:24 AM
  2. [SOLVED] Delete Cell Numbers of Column B From Column A (Column B Small and Column A too big)
    By it_electronics2000 in forum Excel General
    Replies: 4
    Last Post: 04-07-2012, 09:14 AM
  3. [SOLVED] Delete Cell Numbers of Column B From Column A (Column B Small and Column A too big)
    By it_electronics2000 in forum Excel General
    Replies: 5
    Last Post: 04-06-2012, 12:02 PM
  4. [SOLVED] Delete Cell Numbers of Column B From Column A (Column B Small and Column A too big)
    By it_electronics2000 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-06-2012, 11:18 AM
  5. [SOLVED] Delete Cell Numbers of Column B From Column A (Column B Small and Column A too big)
    By it_electronics2000 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-06-2012, 11:13 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1