+ Reply to Thread
Results 1 to 10 of 10

summing a column, every 13th cell only

  1. #1
    Registered User
    Join Date
    10-17-2021
    Location
    philadelphia
    MS-Off Ver
    unsure
    Posts
    10

    summing a column, every 13th cell only

    in a column, if i want to sum up every 13th cell moving down, i.e. starting at D5, then D18, D31, etc., how would i do that?

    formulas i have seen during searches are not working for me

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: summing a column, every 13th cell only

    Do you want running sum?

    If start from row 5:

    =IF(MOD(ROW(5:5)-5,13)=0,SUMPRODUCT((MOD(ROW($5:5)-5,13)=0)*$D$5:D5),"")
    Attached Images Attached Images
    Quang PT

  3. #3
    Registered User
    Join Date
    10-17-2021
    Location
    philadelphia
    MS-Off Ver
    unsure
    Posts
    10

    Re: summing a column, every 13th cell only

    very close, i dont need a running sum, just one at the top of the column or in another single cell

    and want it so it continues every 13th cell down for a long run, as it could vary, but be more than i would want to continuously type in

    thanks!
    not sure if this screenshot uploaded

    Attachment 752052

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: summing a column, every 13th cell only

    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    10-17-2021
    Location
    philadelphia
    MS-Off Ver
    unsure
    Posts
    10

    Re: summing a column, every 13th cell only

    this works, thank you so much!

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: summing a column, every 13th cell only

    Glad to help & thanks for the feedback.

  7. #7
    Registered User
    Join Date
    10-17-2021
    Location
    philadelphia
    MS-Off Ver
    unsure
    Posts
    10

    Re: summing a column, every 13th cell only

    one other thing. this works great for one column, and i could use that formula on a summary tab as well, and it still worked. If i wanted to add up the column D's from several tabs, how would i do that?
    For example:
    This lets me capture every the sum of 13th cell in Column D from another tab (ADMIN) on a front cover sheet summary tab.
    =sumproduct((MOD(ROW(ADMIN!D5:D1000)-5,13)=0)*ADMIN!D5:D1000)
    But if i wanted to have the sum from several tabs, each the same Column D, every 13th cell, how would i do something like that?

    The below doesnt work, and was just a long shot from my remedial excel formula knowledge
    =sumproduct((MOD(ROW(ADMIN!D5:D1000)-5,13)=0)*ADMIN!D5:D1000),((MOD(ROW(ADMIN2!D5:D1000)-5,13)=0)*ADMIN2!D5:D1000),((MOD(ROW(ADMIN3!D5:D1000)-5,13)=0)*ADMIN3!D5:D1000)

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: summing a column, every 13th cell only

    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    10-17-2021
    Location
    philadelphia
    MS-Off Ver
    unsure
    Posts
    10

    Re: summing a column, every 13th cell only

    this is perfect, thank you!

  10. #10
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: summing a column, every 13th cell only

    My pleasure.

+ 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. [SOLVED] How to calculate 13th month
    By Santhoshhrishi in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-01-2019, 08:57 PM
  2. [SOLVED] VBA - Summing column excluding first cell
    By thisandthat in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-21-2017, 07:47 PM
  3. Summing cell content in every 6th column
    By mgerada in forum Excel General
    Replies: 2
    Last Post: 08-14-2011, 10:49 AM
  4. Summing Every 7th Cell in a Column
    By dcm6276 in forum Excel General
    Replies: 1
    Last Post: 04-13-2010, 07:36 PM
  5. [SOLVED] Summing every other cell in a column?
    By RJH in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-12-2006, 10:25 PM
  6. Sum function that add every 13th row...
    By Greg in forum Excel General
    Replies: 9
    Last Post: 03-08-2006, 03:10 PM
  7. Replies: 3
    Last Post: 04-17-2005, 06:13 PM

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