+ Reply to Thread
Results 1 to 10 of 10

sum if to infinity where the criteria is one row offset from the criteria range

  1. #1
    Registered User
    Join Date
    09-26-2015
    Location
    Sherwood Park, Canada
    MS-Off Ver
    Home 2011, Work 2016
    Posts
    52

    sum if to infinity where the criteria is one row offset from the criteria range

    Hello,

    I am trying to use a =sumifs formula where the criteria range searches to infinity (B:B), but then looks one row offset for the criteria. The purpose is to look at the date paid (column A) and sum the amount paid if in USD (Column J). The payment type (eg USD) is labeled in Column I. payment Column I & J are one row down from the the date paid.


    for example:

    =SUMIFS(J:J,A:A,TODAY(),I:I,"USD") works because there is no offset

    But =SUMIFS(J:J,offset(A:A,-1,0),TODAY(),I:I,"USD") does not work because of the offset

    however, =SUMIFS($J2:$J1000,OFFSET($A2:$A1000,-1,0),TODAY(),$I2:$I1000,"USD") works, but I need the formula to sum to infinity.

    Anyone have any ideas?

    Thanks!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: sum if to infinity where the criteria is one row offset from the criteria range

    What exactly are ou trying to offset here? I am having a hard time visualizing what your layout looks like, can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    09-26-2015
    Location
    Sherwood Park, Canada
    MS-Off Ver
    Home 2011, Work 2016
    Posts
    52

    Re: sum if to infinity where the criteria is one row offset from the criteria range

    That's fair, haha.

    I've attached a sample sheet. I've removed some sensitive data so some of the columns have shifted, but I think it should be pretty self explanatory

    Let me know if you need it to be clearer.
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: sum if to infinity where the criteria is one row offset from the criteria range

    OK, so what are you trying to do...sum based on USD?

    Im not following why (or what) you need to offset?

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: sum if to infinity where the criteria is one row offset from the criteria range

    I would not advise your "infinity" approach with OFFSET which is volatile.

    Either set range to credible maximum (as you have 1 million+ rows) OR (better) move the the payment option (USD) to the "date" rows OR add date into "USD" line.

    Why make life difficult?
    Last edited by JohnTopley; 02-10-2017 at 04:38 AM.

  6. #6
    Registered User
    Join Date
    09-26-2015
    Location
    Sherwood Park, Canada
    MS-Off Ver
    Home 2011, Work 2016
    Posts
    52

    Re: sum if to infinity where the criteria is one row offset from the criteria range

    Hello.

    To be a bit more clear I have attached another sales sheet with notes that summarizes what I wish to achieve and why.

    JohnTopley: The reason I do not want to set the range to a credible maximum is because thousands of rows are deleted every month and meaning that I constantly have to repair the formula. I would like to avoid doing this in case I leave the company.
    The reason I don't want to put the payment date and the USD on the same line is simply because this is how it has always been done and all of our staff are used to doing it this way. The payment amount is placed on the row below, this separates our sales. I would like to see if I can find a formula to suits what I want to before changing the layout on all of our employees and that will lead to an adjustment period.

    I know it's a bit complicated, but does anyone have any ideas?
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: sum if to infinity where the criteria is one row offset from the criteria range

    If you make the final range value very large you should not need to amend the formula:


    =SUMIFS($I$2:$I$1000000,OFFSET($A$2:$A$1000000,-1,0),TODAY(),$G$2:$G$1000000,"USD")

    so this will cater for 1000s of lines being deleted unless you are approaching 1 million lines!

  8. #8
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: sum if to infinity where the criteria is one row offset from the criteria range

    Try to use INDEX as,

    =SUMIFS(H2:INDEX(H:H,5000),A1:INDEX(A:A,4999),TODAY(),G2:INDEX(G:G,5000),"USD")

    ... will always stick to row 1:5000 regardless of add/remove of rows. -1 in red highlighted.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: sum if to infinity where the criteria is one row offset from the criteria range

    @Haseeb: another lesson for me: very neat.

  10. #10
    Registered User
    Join Date
    09-26-2015
    Location
    Sherwood Park, Canada
    MS-Off Ver
    Home 2011, Work 2016
    Posts
    52

    Re: sum if to infinity where the criteria is one row offset from the criteria range

    Hey Haseeb A,

    That works perfectly! Thanks so much!

+ 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. Find cells, copy range with criteria and paste with offset
    By benfontein in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 03-13-2017, 03:25 AM
  2. Replies: 5
    Last Post: 10-03-2016, 03:39 AM
  3. [SOLVED] SUMIFS with a dynamic, offset criteria range
    By adelcap in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-22-2014, 05:35 AM
  4. [SOLVED] Sum an offset range where column criteria met.
    By stuart010 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-04-2014, 09:20 AM
  5. Replies: 1
    Last Post: 09-03-2013, 07:41 PM
  6. [SOLVED] Find a number closest to 30 in a range with one more criteria (criteria includes text)
    By Sarangsood in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-22-2013, 01:16 PM
  7. How to sum a range of offset ranges based on criteria
    By beaumonr in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-31-2012, 07:58 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