# SumIf and Offset formula

1. ## 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!

3. ## 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!

4. ## 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),"<>")

5. ## Re: SumIf and Offset formula

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

6. ## 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. ## Re: SumIf and Offset formula

Alternative formula avoiding OFFSET violatile function:

``Please Login or Register  to view this content.``

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