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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
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

Active Member
Joined
Apr 15, 2003
Messages
343
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

Board Regular
Joined
Aug 19, 2006
Messages
176
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

Active Member
Joined
Apr 15, 2003
Messages
343

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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

Active Member
Joined
Apr 15, 2003
Messages
343

ADVERTISEMENT

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

Board Regular
Joined
Aug 19, 2006
Messages
176
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi again

With the data in your example try in C2:

Code:
=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
 

Forum statistics

Threads
1,136,618
Messages
5,676,844
Members
419,655
Latest member
pd2021vb

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
Top