Results 1 to 8 of 8

Thread: First Nth Column Sums
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2018
    Posts
    76
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default First Nth Column Sums

    Hi All,


    Just trying to get a formula in workbook A to get Month to date figures from the data below in workbook B, say I need to get the Apple figure up to May, so in workbook A i had May in the title cell A1 but in A2 i should use the formula to get the sum of $26. I tried sum(if but this can only add one column rather than multiple columns, also tried sum(offset but this does not work with the data in a different workbook data and lastly tried to concatenate sum formula from Range Column A to Nth Column dependence on the month but cannot get the sting into a formula even using indirect. Any better suggestion would be much appreciated. thanks for your help. Cheers, Peter




    A B C D E F G H I J K L M
    1 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
    2 Apple 7 1 2 10 6 6 10 6 2 1 9 10
    3 Orange 2 9 4 2 8 8 10 3 2 2 4 8
    4 Mango 6 2 6 5 3 0 5 2 7 5 9 6
    Last edited by peter8848; Aug 9th, 2019 at 01:13 AM.

  2. #2
    Board Regular
    Join Date
    Feb 2005
    Location
    Melbourne (Australia)
    Posts
    578
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: First Nth Column Sums

    Can you edit Workbook B?

    If so, an extra row with 1-12 instead of Jan, Feb etc would allow you to use SUMIF().
    "I'm almost sure I'm not mad..." - Stoppard

  3. #3
    Board Regular
    Join Date
    Feb 2005
    Location
    Melbourne (Australia)
    Posts
    578
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: First Nth Column Sums

    Why wouldn't OFFSET work for you?

    I got it to work with this:

    =SUM(OFFSET('[Workbook B.xlsx]Sheet1'!$B$2,0,0,1,MATCH(A1,'[Workbook B.xlsx]Sheet1'!$B$1:$M$1,0)))
    "I'm almost sure I'm not mad..." - Stoppard

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,314
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: First Nth Column Sums

    Another option
    AB
    1May
    2Apple26

    Paste



    Worksheet Formulas
    CellFormula
    B2=SUMPRODUCT(([Book1]Sheet1!$A$2:$A$4=A2)*(MONTH([Book1]Sheet1!$B$1:$M$1&1)<=MONTH(B1&1)),[Book1]Sheet1!$B$2:$M$4)

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Oct 2018
    Posts
    76
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: First Nth Column Sums

    Hi Glove_Man,

    Thanks for your reply.

    It just i some post saying it will not work with external workbook while i could not work it out. Also could yours work in a range like what Flutt had? Just want to learn if possible and what does that ,0,0,1 stand for in "Sheet1'!$B$2,0,0,1"?

    Cheers,

    Peter

  6. #6
    Board Regular
    Join Date
    Oct 2018
    Posts
    76
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: First Nth Column Sums

    Hi Fluff,

    You are a champion and it works!!!!

    Sorry just wondering what is that &1 before the two brackets stand for? thanks for your help again.

    Cheers,

    Peter

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,314
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: First Nth Column Sums

    You're welcome & thanks for the feedback
    It enables xl to convert a text month such as Aug into a number (ie 8)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular
    Join Date
    Oct 2018
    Posts
    76
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: First Nth Column Sums

    Hi Fluff,

    Sorry one more question please, if is possible to amend your formula a bit to get the average of these value instead of the sum?

    Thanks in advance.

    Peter

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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