+ Reply to Thread
Results 1 to 8 of 8

Help defining range SUM and OFFSET together

  1. #1
    Registered User
    Join Date
    02-24-2016
    Location
    Amsterdam, Netherlands
    MS-Off Ver
    2010
    Posts
    3

    Help defining range SUM and OFFSET together

    Hello ExcelForum members,

    I want to formulate a function that describes the following:

    The sum of all cells, 4 columns left of all cells with the value 10.

    I searched the internet and I found the OFFSET function. But in this case I get stuck at =SUM(OFFSET(.....
    I don't know how to define (4 cells to the left of every cell containing the value 10)

    Can someone help me?

    I look forward to any help!
    Thanks in advance,

    Dyoerdt
    Last edited by dyoerdt; 02-24-2016 at 11:02 AM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: What function to use in this situation?

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help defining range SUM and OFFSET together

    Can you post a SMALL sample file and show us what result you expect?

    A SMALL file will have about 20 rows worth of data.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    02-24-2016
    Location
    Amsterdam, Netherlands
    MS-Off Ver
    2010
    Posts
    3

    Re: Help defining range SUM and OFFSET together

    Hi Tony,

    I am not sure how to post a table so I made a screenshot. I hope this helps.
    BTW in this case it is 1 column to the left but in my original file I need 4 columns to the left

    Capture.PNG

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,657

    Re: Help defining range SUM and OFFSET together

    Unless the column with the 10s is dynamic, use SUMIF

    =SUMIF(E2:E100,10,A2:A100)

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help defining range SUM and OFFSET together

    Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.

    If you need to post an image post it in the *.jpg format.

  7. #7
    Registered User
    Join Date
    02-24-2016
    Location
    Amsterdam, Netherlands
    MS-Off Ver
    2010
    Posts
    3

    Re: Help defining range SUM and OFFSET together

    Dear JohnTopley,

    Thank you so much I was thinking in the complete wrong direction. But now my problem is solved.

    Thank you for your help!

    Dyoerdt

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help defining range SUM and OFFSET together

    I was able to view the image using alternative steps.

    Here's what I think you want...

    Data Range
    A
    B
    C
    D
    E
    1
    Days Absent
    Week Number
    ------
    Week
    Absenteeism per week
    2
    1
    8
    7
    0
    3
    2
    8
    8
    3
    4
    3
    9
    9
    3
    5
    4
    10
    10
    15
    6
    5
    10
    11
    15
    7
    6
    10
    12
    9
    8
    7
    11
    13
    10
    9
    8
    11
    14
    0
    10
    9
    12
    15
    0
    11
    10
    13
    16
    0


    This formula entered in E2 and copied down:

    =SUMIF(B$2:B$11,D2,A$2:A$11)

+ 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. Replies: 1
    Last Post: 07-22-2014, 09:40 PM
  2. [SOLVED] What is the right IF Function I should use for this situation?
    By MGadAllah in forum Excel Formulas & Functions
    Replies: 26
    Last Post: 08-25-2013, 12:03 AM
  3. [SOLVED] Dragging down to copy function situation
    By Richard N in forum Excel General
    Replies: 2
    Last Post: 04-17-2013, 09:52 AM
  4. Which function do I use for this situation?
    By korygrandy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-05-2008, 12:01 PM
  5. #N/A situation
    By bnwash in forum Excel General
    Replies: 5
    Last Post: 07-18-2007, 04:03 AM
  6. Can I use this situation?
    By tonyrice in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-22-2007, 12:57 PM
  7. [SOLVED] need help with a If=then situation.
    By FireBrick in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-02-2005, 11:06 AM

Tags for this Thread

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