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

[jkeyes]

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
	A	B	C	D	E	F	G	H
1	Issue Date	Total	Aug-06	Sep-06	Oct-06	Nov-06	Dec-06	Jan-07
2	Sep-06	134.33	0.02	43.34	90.97
3	Oct-06	248.93		0.04	84.89	164.00
4	Nov-06	1,478.72			0.24	504.29	974.19
5	Dec-06	495.86				0.08	169.10	326.68
6
7			- Need to fill with formula to retrieve monthly subtotals from Sheet2
Sheet1

Sheet2:

Book4
	A	B	C	D	E	F	G	H
1	Book	Issue Date	0	1	2		Col Headers
2	1	Sep-06	0.02	43.34	90.97		0	- Issue Date minus 1 Month
3	2	Oct-06	0.01	20.99	40.55		1	- Issue Date Month
4	3	Oct-06	0.03	63.90	123.44		2	- Issue Date plus 1 Month
5	4	Nov-06	0.07	144.15	278.48
6	5	Nov-06	0.07	138.37	267.31
7	6	Nov-06	0.11	221.76	428.40
8	7	Dec-06	0.08	169.10	326.68
Sheet2

 

[Dufus]

jkeyes.

I really think the best solution to your problem is to lay out the data in standard table format so you can use native functionality to analyze the data. For example, it may seem easier to enter the data under columns marked "0","1","2" but scrolling to the right to enter the data in a column marked with the proper month (as in the Sheet1 layout) wouldn't really cause that much of a problem and would solve a slew of problems.

If there was one record for each book and the amounts were under column headers like in Sheet1, you could just use a Pivot Table to summarize the data and you could start to look at the data in many different ways. Think about it.

Dufus

 

[jkeyes]

Thanks for the reply.

I completely agree with you on your suggestion. Unfortunately, I don't have a choice on how the source data (Sheet2) is laid out. As you can see, this is what's causing my headache now.

So, given that, any ideas on a formula?

Thanks again!

 

[Dufus]

In that case, can you add a Sheet3 that duplicates Sheet2 in all regards except to the right of the "0", "1", "2" table are columns representing the date columns in Sheet1? Then you could use a Pivot Table on Sheet3 to report and analyze the data.

 

[jkeyes]

I see where you're going with this, but if I'm understanding you correctly, I still have my basic problem to solve, which is essentially, how do I get the 4 col. x 7 row table in Sheet2 into a waterfall format based on Issue Date. Once I'm there, it's just sub-totaling/sumif...

I'm fairly certain there's a solution using a formula with some combination of vlookup/match/sumif, and probably an array... I'm just at my wits end on what it'd be.

But I do appreciate the help and out-of-the-box thinking!

 

[pgc01]

Hi jkeyes

We only see the month and the year. How is date specified, is it always the same day every month (ex. 1st) or are dates entered with different days each month (ex. end of the month 28,29,30,31).

That's to understand how I can compare the dates.

Kind regards
PGC

 

[jkeyes]

Sorry, good question, it's just how they're formatted... every date is the first of the month. So, Sep-06 is 09-01-2006. Thanks!

 

[Dufus]

Just to be clear, the format of the report in Sheet1 is standard issue for a Pivot Table based on what I described for Sheet3. The formulas in Sheet3 are simple IF(TheDateInColumnA=TheDateInTheHeaderRowAboveMinusOneMonth,AmountInColumnB), IF(TheDateInColumnA=TheDateInTheHeaderRowAbove,AmountInColumnC,IF(TheDateInColumnA=TheDateInTheHeaderRowAbovePlusOneMonth,AmountInColumnD,0) nested IFs.

But, I'm sure you're right. There is a formula to combine all that into the layout of the report in Sheet1.

 

[pgc01]

Hi again

With the data in your example try in C2:

=SUM(IF(C$1=DATE(YEAR($A2),MONTH($A2)+{-1,0,1},DAY($A2)),IF(Sheet2!$B$2:$B$8=$A2,OFFSET(Sheet2!$C$2:$E$8,-1+ROW($1:$8),{0,1,2}))))

This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.
Copy down and accross.

HTH
PGC

 

[jkeyes]

That's it! Thank you, thank you, thank you!