+ Reply to Thread
Results 1 to 8 of 8

Sumif and offset formula that does not use arrays?

  1. 10-31-2015, 06:18 PM #1
    PaulG2015
    PaulG2015 is offline
    Registered User
    Join Date
    10-31-2015
    Location
    England
    MS-Off Ver
    2007
    Posts
    5

    Sumif and offset formula that does not use arrays?

    Hi guys and thank you for reading my thread.

    I am having some trouble coming up with a formula for summing cells based on certain criteria. I am familiar with SUMIF and IF functions and OFFSET to some extent, but I have managed to get a bit confused with the choice of formulas available and whether they can be used to achieve what I would like. I have tried exhaustive searches (through Google, not on this forum) and there's an overwhelming number of articles about functions but none that are specific to what I am looking for. Sadly I have exhausted my Excel skills (it didnt take long!) and wondered if someone might be able to point me in the right direction?

    I have a large table in Excel, and I am trying to sum certain values in a row if the value eight columns to the left is equal to the value "Node 1". Its the equivalent of using the formula (in Cell A1):

    =IF(B1="Node 1",J10,0)+IF(K1="Node 1",S1,0).....etc.

    Unfortunately I have a large number of columns to repeat this for and it would quite cumbersome to write out and store this formula for a long range of cells. Is there a neater version that could achieve the same result?

    I looked at sumif formulas, but these work on the "Node 1" values and the value I am trying to sum being in different rows, and I also looked at array-based sumifs, but I need to copy this formula down over many rows and my understand is that array formulas cannot be copy-pasted.

    I also thought of using some kind of match/index type function with an offset, but I think this will only work for the first instance that the formula finds, and there may be many instances of "Node 1" in the particular row that I am looking at.

    Would anyone be able to offer any ideas? Am I overthinking this?

    Thank you for any help anyone can offer me.

    Kind regards,

    Paul
    Reply With QuoteRegister To Reply
  2. 10-31-2015, 06:27 PM #2
    gmr4evr1
    gmr4evr1 is offline
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Sumif and offset formula that does not use arrays?

    Can you post a sample of what you have and what you want the result to be. I've either got an idea of what you want, or I'm just a lil confused

    *EDIT: Or, just try out what Jeffery has posted, it will probably be faster.
    Last edited by gmr4evr1; 10-31-2015 at 06:37 PM.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom
    Reply With QuoteRegister To Reply
  3. 10-31-2015, 06:29 PM #3
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Sumif and offset formula that does not use arrays?

    Try

    =SUMIF($B$1:$K$1,"Node 1",$J$1:$S$1)

    expand your ranges to fit your needs
    HTH
    Regards, Jeff
    Reply With QuoteRegister To Reply
  4. 11-01-2015, 05:41 AM #4
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Sumif and offset formula that does not use arrays?

    Use a pivot table.

    Since you don't add a small excel file, without confidential information, I can't show you.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  5. 11-01-2015, 06:39 AM #5
    PaulG2015
    PaulG2015 is offline
    Registered User
    Join Date
    10-31-2015
    Location
    England
    MS-Off Ver
    2007
    Posts
    5

    Re: Sumif and offset formula that does not use arrays?

    Hi guys,

    Thank you for the (very fast!) responses.

    The solution posted by gmr4evr1 does the trick and is a very elegant solution. It never occurred to me that I could just offset the "sum range" versus the "criteria range" to achieve what was needed.

    Thank you so much for your help!

    Kind regards,

    Paul
    Reply With QuoteRegister To Reply
  6. 11-01-2015, 09:19 AM #6
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Sumif and offset formula that does not use arrays?

    Hi Paul,

    If this satisfies your query, please mark the thread as solved.

    • Click Thread Tools above your first post
    • Select "Mark your thread as Solved"

    Or

    • Click the Edit button on your first post in the thread
    • Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes

    NOTE: If more than two days have passed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.
    Reply With QuoteRegister To Reply
  7. 11-01-2015, 10:30 AM #7
    gmr4evr1
    gmr4evr1 is offline
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Sumif and offset formula that does not use arrays?

    Just a clarification, it was Jeffrey that posted the solution.
    Reply With QuoteRegister To Reply
  8. 11-01-2015, 03:55 PM #8
    PaulG2015
    PaulG2015 is offline
    Registered User
    Join Date
    10-31-2015
    Location
    England
    MS-Off Ver
    2007
    Posts
    5

    Re: Sumif and offset formula that does not use arrays?

    Oops! Apologies!

    Thank you Jeffrey for the solution - Ill mark this issue as resolved.

    Cheers,

    Paul
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] ARRAYS and OFFSET() to update a spreadsheet
    By pnmng49 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-10-2014, 05:52 PM
  2. [SOLVED] Offset, Arrays, and more Offset
    By lucholland in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-11-2014, 09:22 PM
  3. SUMIF with subtacting arrays
    By Zeos6 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-08-2013, 06:38 PM
  4. [SOLVED] offset multiple arrays
    By mammagamma in forum Excel General
    Replies: 9
    Last Post: 07-03-2012, 01:48 PM
  5. SUMIF Multiple arrays in 2003
    By snoproladd in forum Excel General
    Replies: 0
    Last Post: 02-20-2012, 03:50 PM
  6. Sumif using arrays, yet with one condition within a string
    By ormation in forum Excel General
    Replies: 1
    Last Post: 03-15-2009, 11:44 PM
  7. [SOLVED] Use of offset and match functions with changing arrays, I think???
    By Prohock in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-15-2006, 01:20 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1