+ Reply to Thread
Results 1 to 13 of 13

SUM with OFFSET of multiple criteria

  1. #1
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    SUM with OFFSET of multiple criteria

    USA Canada England China Australia
    2016 Q1 12 12 8 49 18
    Q2 23 54 4 89 43
    Q3 12 15 16 20 32
    Q4 1 2 3 4 5
    2017 Q1 9 8 7 6 5
    Q2 10 13 15 16 20
    Q3 20 22 26 28 39
    Q4 21 22 10 16 18

    Given the above table, how can I sum for the year of 2017 for ONLY USA and England?

    I tried using an array formula below but failed.

    Please Login or Register  to view this content.

    Formula was working with SUM and OFFSET given the ISNUMBER and MATCH formula from AlKey

    Please Login or Register  to view this content.
    Last edited by dluhut; 05-17-2017 at 02:09 PM.

  2. #2
    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,900

    Re: SUM with OFFSET of multiple criteria

    Fill the years into the blank cells in column A and use this one:

    =SUMPRODUCT(($A$2:$A$9=2017)*(($C$1:$G$1="USA")+($C$1:$G$1="England"))*$C$2:$G$9)
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 05-17-2017 at 12:58 PM. Reason: Adding attachment
    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

  3. #3
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: SUM with OFFSET of multiple criteria

    First, fill in the blank cells in column A. Then you can use a formula like this:

    =SUMPRODUCT((A2:A9=2017)*((C1:G1="USA")+(C1:G1="England"))*(C2:G9))

  4. #4
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: SUM with OFFSET of multiple criteria

    Is there a way not to use the above formula, particularly,
    Please Login or Register  to view this content.
    ?

    Reason being, it'll be dynamic for the 'years' and the 'countries'

  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,900

    Re: SUM with OFFSET of multiple criteria

    That depends what you mean. You can, ofd course have 2017, USA and England entered in cells and then adjust the formula...
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: SUM with OFFSET of multiple criteria

    Quote Originally Posted by Glenn Kennedy View Post
    That depends what you mean. You can, ofd course have 2017, USA and England entered in cells and then adjust the formula...
    Well, first thing is that I can't edit the 'year' cell to fill up the blanks as it's protected.

    Secondly, the user had made it to be dynamic in a sense that when user select values from the user form that was created, the country value will be listed.

    Given the limited access to the file, that's why I'm thinking of using a formula that was purposefully left out for users to do 'manual' calculations. Instead of manually calculating, I'm thinking of having a formula where I'm currently stuck with.

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: SUM with OFFSET of multiple criteria

    The literal 2017 and destinations can be replaced with cell references. This needs to be array entered. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

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

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: SUM with OFFSET of multiple criteria

    Also this works non-array.

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

  9. #9
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: SUM with OFFSET of multiple criteria

    Maybe this?
    Where countries USA and England as criteria located in cells I6:I7
    and SUM in H6
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    USA Canada England China Australia SUM Country
    2
    2016
    Q1
    12
    12
    8
    49
    18
    3
    Q2
    23
    54
    4
    89
    43
    4
    Q3
    12
    15
    16
    20
    32
    5
    Q4
    1
    2
    3
    4
    5
    6
    2017
    Q1
    9
    8
    7
    6
    5
    118
    USA
    7
    Q2
    10
    13
    15
    16
    20
    England
    8
    Q3
    20
    22
    26
    28
    39
    9
    Q4
    21
    22
    10
    16
    18
    Sheet: Sheet1
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: SUM with OFFSET of multiple criteria

    @ AlKey

    Don't you need to resolve the year to get the C$6:G$9 range?

  11. #11
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: SUM with OFFSET of multiple criteria

    Quote Originally Posted by AlKey View Post
    Maybe this?
    Where countries USA and England as criteria located in cells I6:I7
    and SUM in H6
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    USA Canada England China Australia SUM Country
    2
    2016
    Q1
    12
    12
    8
    49
    18
    3
    Q2
    23
    54
    4
    89
    43
    4
    Q3
    12
    15
    16
    20
    32
    5
    Q4
    1
    2
    3
    4
    5
    6
    2017
    Q1
    9
    8
    7
    6
    5
    118
    USA
    7
    Q2
    10
    13
    15
    16
    20
    England
    8
    Q3
    20
    22
    26
    28
    39
    9
    Q4
    21
    22
    10
    16
    18
    Sheet: Sheet1
    Was able to use the SUM and OFFSET formula given with your ISNUMBER and MATCH formula. And it worked!!!

    Question though, why is it that on the MATCH formula, the lookup value and the lookup array is reversed? I thought the lookup value is supposed to be $I$6:$I$7 and lookup array is supposed to be $C$1:$G$1, but it's not the case for this?

    PS: Reps up to you!

  12. #12
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: SUM with OFFSET of multiple criteria

    Quote Originally Posted by FlameRetired View Post
    @ AlKey

    Don't you need to resolve the year to get the C$6:G$9 range?
    Using the OFFSET, was able to resolve the year

  13. #13
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: SUM with OFFSET of multiple criteria

    Cool.__________________

+ 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] sumproduct subtotal offset with multiple criteria
    By reneevic in forum Excel General
    Replies: 10
    Last Post: 07-02-2019, 04:57 PM
  2. [SOLVED] Multiple IF, OFFSET, MATCH criteria
    By Nutzman in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-16-2017, 05:52 PM
  3. Offset / Multiple Match criteria
    By JamesArmitage in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-09-2016, 09:42 AM
  4. Replies: 5
    Last Post: 02-06-2015, 08:44 PM
  5. [SOLVED] Need Userform to Write to Multiple Sheets with Different Offset Criteria
    By eyeguy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-22-2014, 10:25 AM
  6. [SOLVED] Lookup with multiple criteria and offset
    By sasindiv in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-14-2013, 10:17 PM
  7. [SOLVED] Offset weeks with multiple criteria.
    By Sthlm in forum Excel General
    Replies: 5
    Last Post: 06-22-2012, 10:48 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