+ Reply to Thread
Results 1 to 7 of 7

SumIf and Offset formula

  1. #1
    Registered User
    Join Date
    05-26-2021
    Location
    California
    MS-Off Ver
    MS 365
    Posts
    6

    Question SumIf and Offset formula

    Hello,

    I am trying to get the sum of a range based on a dropdown menu option, that chooses from a data sheet. So I have a data sheet that has all the days of the year listed, and depending on the week, another column that says "MW1", "MW2", etc. so days 1-7 are all listed as MW1 and 7-14 are MW2 and so on.

    What I am trying to do is first i have a sumif formula that pulls from the dropdown list (which is all the MW1,MW2,MW3,etc), so if i select MW2 it sums all the numbers from the data sheet column I selected that have the dates and week listed of MW2. What i want to do now is compare this week I selected with the week prior, so my thought was to do a Sumif formula and then offset the range up 7 days prior. Nothing has worked and I'm not sure i am structuring this properly. I first tried this:

    =SUMIF('Daily KPIs New'!A:A,K2,OFFSET('Daily KPIs New'!D:D,-7,0,7)) where K2 is the drop down list, and it sums the ranges from the data sheet.

    Then after some research I saw sumif + offset formulas look like this, =SUMIF(OFFSET('Daily KPIs New'!D:D,-7,0,7),K2,'Daily KPIs New'!D:D) but i don't think the offset knows where to look or start based on this. I am new to advanced excel and forums so any tips would be helpful! If this is not the best way to go about it, let me know!

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,892

    Re: SumIf and Offset formula

    Answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

  3. #3
    Registered User
    Join Date
    05-26-2021
    Location
    California
    MS-Off Ver
    MS 365
    Posts
    6

    Re: SumIf and Offset formula

    attached a sample of the worksheet. So if i have MW7 selected in the dropdown on E2, B2 is summing the page visits for that week, and I'd like if C2 would show the sum of the traffic page visits for MW6. The answer should be 16,657 in C2

    Thank you!
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    EXCEL 2007/365
    Posts
    1,125

    Re: SumIf and Offset formula

    PLS TRY

    =SUMPRODUCT(OFFSET('Daily KPIs New'!$D:$D,MATCH(E2,'Daily KPIs New'!$A:$A,)-1,,7))

    OR

    =SUMIF(OFFSET('Daily KPIs New'!$D:$D,MATCH(E2,'Daily KPIs New'!$A:$A,)-1,,7),"<>")
    Last edited by wk9128; 05-27-2021 at 10:52 PM.

  5. #5
    Registered User
    Join Date
    05-26-2021
    Location
    California
    MS-Off Ver
    MS 365
    Posts
    6

    Re: SumIf and Offset formula

    The second one worked, thank you! what are the "<>" purpose?

  6. #6
    Valued Forum Contributor wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    EXCEL 2007/365
    Posts
    1,125

    Re: SumIf and Offset formula

    The first formula can also be used, SUM (required array formula) and SUMPRODUCT have been updated in POST #4

    ANS : "<>" purpose meaning Not empty

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,990

    Re: SumIf and Offset formula

    Alternative formula avoiding OFFSET violatile function:

    Please Login or Register  to view this content.

+ 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] Offset and sumif combined formula?
    By Badvgood in forum Excel General
    Replies: 4
    Last Post: 10-06-2020, 05:00 AM
  2. Problem with OFFSET in the sum range of a SUMIF formula
    By wanwan79 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-13-2017, 01:17 PM
  3. [SOLVED] SUMIF: don't understand that purpose of the OFFSET inside SUMIF
    By Vitalite in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-30-2017, 03:13 AM
  4. [SOLVED] Sumif and offset formula that does not use arrays?
    By PaulG2015 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-01-2015, 03:55 PM
  5. SUMIF with OFFSET
    By kak132 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-17-2014, 03:25 AM
  6. Sumif & Offset together
    By Dial1 in forum Excel General
    Replies: 3
    Last Post: 08-04-2010, 06:53 PM
  7. Using sumif and offset together
    By katie1343 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-11-2007, 12:32 PM

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