# Sum if + vlook up

1. ## Sum if + vlook up

Hi everyone,

I'd be grateful if someone could lend their expertise on a sum if + vlook up formula. I have a report with weeks, 1-52, for multiple years listed vertically, with some data in an adjacent column. I'd like to create a formula to sum certain rows based on the periods the weeks relate to (for example, weeks 1-4 relate to period 1, so i'd want to only sum the rows for weeks 1-4)

Sure, i can do a simple sum if formula, however to span weeks covering a number of years, it would involve quite a bit of manual manipulation to change the sum if statements to relate to the required period. I'd quite like to use a formula that i can roll down the weeks over the 4x years worth of weeks data i have.

I was thinking i'd need a separate look up table to show what weeks belong to what period (i.e. wk1-4 = period 1), but can't quite get it to work

Any help appreciated!
Motley

2. ## Re: Sum if + vlook up

It would help if you attached a sample Excel workbook . Not a image.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Do not try to use the Paperclip icon, as this does not work on this forum.

Hope this helps.

3. ## Re: Sum if + vlook up

Hi Caracalla,

OK, thanks for this piece of info. Attached is a sample file with dummy data in based on weeks of different years, then a tab for a look up with the periods the weeks relate to.
I want to create a sum if / v look up that sums the weeks based on the period they relate to

Motley

4. ## Re: Sum if + vlook up

H2=SUMIFS(\$C\$1:\$C\$1000,data!\$B\$1:\$B\$1000,">="&MINIFS('look up'!\$A\$2:\$A\$53,'look up'!\$B\$2:\$B\$53,data!E2),data!\$B\$1:\$B\$1000,"<="&MAXIFS('look up'!\$A\$2:\$A\$53,'look up'!\$B\$2:\$B\$53,data!E2),\$A\$1:\$A\$1000,F2)

5. ## Re: Sum if + vlook up

If you wanted to get a summary table of all the periods and years, you can use this formula in D1:

=VLOOKUP(B1,'look up'!A:B,2)&"_"&LOOKUP(10000000,A\$1:A1)

then copy this down to the bottom of your list. Then with the years in cells F3 to H3, and the periods 1 to 12 in cells E4 to E15, you can use this formula in F4:

=SUMIFS(\$C:\$C,\$D:\$D,\$E4&"_"&F\$3)

Copy this across and down as required.

I've added some dummy data (in red) in column C to demonstrate this in the attached file, and corrected the final year in column A.

Hope this helps.

Pete

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