Vlookup/Match/Sumif (I think)... - amendment to solution?

jkeyes

Active Member
Joined
Apr 15, 2003
Messages
343
This is my first time using HTMLmaker... hope it works!

What I am trying to do is develop a formula for the shaded cells on Sheet1 that returns the subtotals by month from Sheet2 (I've left the desired results in the shaded area for illustration).

Sheet2 contains monthly values based on when each book was issued (released). The problem is that the books, in reality, release a month earlier than listed (e.g. Issue Date = 9-06, actual release = 8-06), and the source data (Sheet2) is organized in a table based on a book's life cycle (Col. 0 = Issue Date minus 1 Month, Col. 1 = Issue Date Month, and Col. 2 = Issue Date plus 1 Month), rather than in a standard "waterfall" layout.

I'm hitting a wall on developing a formula to accommodate this, and I could really use your help! I think I've explained it well enough, but if not, please ask!

Here are the sheets I'm using:

Sheet1:
Book4
ABCDEFGH
1Issue DateTotalAug-06Sep-06Oct-06Nov-06Dec-06Jan-07
2Sep-06134.330.0243.3490.97
3Oct-06248.930.0484.89164.00
4Nov-061,478.720.24504.29974.19
5Dec-06495.860.08169.10326.68
6
7- Need to fill with formula to retrieve monthly subtotals from Sheet2
Sheet1


Sheet2:
Book4
ABCDEFGH
1BookIssue Date012Col Headers
21Sep-060.0243.3490.970- Issue Date minus 1 Month
32Oct-060.0120.9940.551- Issue Date Month
43Oct-060.0363.90123.442- Issue Date plus 1 Month
54Nov-060.07144.15278.48
65Nov-060.07138.37267.31
76Nov-060.11221.76428.40
87Dec-060.08169.10326.68
Sheet2
 
OK, so I know that pgc01's solution works in the example I posted (great work again, thanks!), but I'm having a little trouble applying it to my actual data. My example was an extremely pared-down version... here's how it actually lays out...

- the months in the Sheet1 header go from May-06 to Dec-09, though from the looks of it I don't think this matters.

- the range of Issue Dates in Sheet2 (Col B) goes from May-06 to Dec-07. But, the important part, I think, is that the number of rows may be dynamic- anywhere from 100 to 2,000. I thought I could just put in the max range of possible rows (e.g. $B$2:$B:2000), but maybe not...

- the life cycle of a book is actually 19 months (not 3), so my column headers on Sheet2 run from 0 to 18. I also have two rows above my actual source data, so my table (with headers) starts on Row 3.

- the source data table (e.g. $C$2:$E:8 on Sheet2) is actually offset quite a ways from the column of Issue Dates. It's actual range is $AU$4:$BM$2000 (not including the 0-18 headers, and assuming the full possible row amount).


Here is how I adapted the formula:

Code:
=SUM(IF(C$1=DATE(YEAR($A2),MONTH($A2)+{-1,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17},DAY($A2)), IF(Sheet2!$B$4:$B$2000=$A2,OFFSET(Sheet2!$AU$4:$BM$2000,-1+ROW($3:$2000),{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18}))))

I thought I adapted it properly, but it's not working... When I applied the formula to the first row (copied across), it returned the sum of the first two rows from the source data table, when it should have just returned the first. The second row has an Issue Date one month later than the first row.

I'm sure it's a minor error on my part, but after 1.5 hours, I'm not seeing it... please help (again)! :)

Thanks!
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hi again.

First let me tell you that I had a typo in my previous formula. Replave row($1:$8) by row($1:$7) since in your previous example you had 7 rows of data in sheet2. This would not cause errors, but is no correct.


Now for your present problem.

You have data in sheet 2 from 1 month before to 17 months later corresponding to the headers 0-18.

Looking at your formula I see an error.

As you know the OFFSET formula allows you to get an address relative to the position of a cell.

In your case the top left cell of your data range is, if I understood well Sheet2!AU4.

Your data table is in Sheet2!$AU$4:$BM$2000 (just the data, without the headers).

From that top left cell you want to access any possible data cell. This means that vertically you'll go from the row offset 0 (the same row as AU4, row 4) untill the row with offset 19996, since the reference cell is AU4 a vertical offset of 19996 gives us the row 20000.

So, what we needed was row($0:$19996) to generate all the necessary offsets. However excel syntax does not allow a row zero, the minimum is 1. So we write -1 + row($1:$19997) and get all the offsets we want.

For the horizontal offset, since we have 19 columns, the offfset will be from column offset 0 (the same column as A4, column AU), until column offset 18, 18 columns to the right of AU, column BM.


So, finally, what I saw wrong immediately in your formula was in the vertical offset

-1+ROW($3:$2000)

this means that you would start at your data table in 2 rows below the first row (-1 +3) and you would end at a vertical offset 19999 already out of the table.

So, replace it with

-1 + row($1:$19997)

to start at vertical offset 0 (the first row of your table) and end at vertical offset 19996 (the last row of your table, since you start in row 4).

This is what I could see without testing. If you still have problems post again with details and I'll try to find other problems tomorrow.


I hope I was not too boring with details
Hope it works
PGC
 
Upvote 0
:pray: Again, THANK YOU! That was it!

And, I truly appreciate the details- not boring at all! This is exactly how we become better at Excel... when we have people like you who take the time to explain how/why these elements work. I get it now, and I have a few more weapons I can add to my Excel arsenal- thanks to you!

If I could, I'd buy you a drink... instead, cheers to you! (y)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top