Free Microsoft Excel 2013 Quick Reference

Sumproduct/Index/Match-for Year To Date or Quarterly results

http://www.ozgrid.com/forum/showthread.php?t=166448

Here is a repost from a question I asked yesterday

I need a formula to show the year to date date depending on the month that is currently in a cell.

Also, if a way to show quarterly data is possibly that would be awesome as well.

Cell F3= data criteria that varies, could be a VP name, cost center, section or a certain plant
Cell A2=Month

The formula below sums using multiple criteria and matches the Month in A2 to column headers in another sheet.

It works great for the current period month, but I need it to also add any months prior to the month referenced in cell A2
I have tried serveral different methods like using


Post your answer or comment

comments powered by Disqus
Hi all
I have a validation list at b3 for diff fiscal period for user to choose
fiscal period layout as
Total,Sept,Oct,Nov,Dec,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug

Cols: D,F,H,J,L,N,P,R,T,V,X,Z,AB

Would like to alter my formula at D5 to sum year to date based on selection
at B3. So If B3 = Sept, D5 = F5.

If B3 = November, D5 = F5 + H5 + J5

Thanks all
-marc

I am trying to create a payroll spreadsheet. I want to get the formula for
year to date.
I would rather not use a 52 column sheet with totals. How can I add the
change each week without using a large sheet?

Hope someone can help....

On sheet 2 of my spreadsheet have a row for each day of the year and and for multiple vessels. The vessel stops at a various locations and either load or discharge inventory. Loading is indentified in columnF with "LOAD". Occasionally, the load may take 2 days (partial load each day).

YrMth.dayShipping MethodVesselPort TypeSteaming TimeLoad/Discharge Time2008 Jan1 STENA CARIBBEANSTENA CARIBBEANDischarge0.000.002008 Jan2 STENA CARIBBEANSTENA CARIBBEAN44.750.002008 Jan3 STENA CARIBBEANSTENA CARIBBEAN2008 Jan4 STENA CARIBBEANSTENA CARIBBEAN2008 Jan5 STENA CARIBBEANSTENA CARIBBEANLoad6.3321.362008 Jan6 STENA CARIBBEANSTENA CARIBBEANLoad4.822008 Jan7 STENA CARIBBEANSTENA CARIBBEAN

On sheet 1 I have a report that list each load by vessel/inv type/inventory volume based on an index match for a given date and then ranking them based on date and listing out the details:

This brings back the vessel -
ColumnQ
=IF(SUM(SUMPRODUCT(--(DataDump!$AY$2:$AY$2969=$N$1),--(DataDump!$W$2:$W$2969>0))+0)0,ROW(DataDump!A$2:A$2969)-ROW(DataDump!A$1)+1)),ROWS(A$1:A1)),5))

This brings back the date -
ColumnR
=IF(SUM(SUMPRODUCT(--(DataDump!$AY$2:$AY$2969=$N$1),--(DataDump!$W$2:$W$2969>0))+0)0,ROW(DataDump!B$2:B$2969)-ROW(DataDump!B$1))),ROWS(B$1:B1)),3))

The issue is when the load occurs over a 2 day period. The formula is listing both days, but I need it to list it once (values in blue above).

STENA CARIBBEAN
5STENA CARIBBEAN6

I wonder if there is a way to add criteria to the index formula to evaluate the row above to see if ColumnF = LOAD, and if so ignore it in the formulas (shown above).

So above should only show Stena Caribbean 5

Thanks for any help.

Hey Guys,

I'm trying to implement a Month-to-date and Year-to-Date Average for my Excel file.

So I figured that I might need to use a SUMPRODUCT function or something along those lines to go out and look for, for example, all data that occurs during October

So basically I want an formula that will include the data that's already plugged in as well as adjust for future dates, as in tomorrow, when I put in 9-Oct information, etc. Thanks!! Hope someone can help, as it would be much appreciated! I would then want to apply a similar formula to adjust for November when November comes around as well, so a versatile formula would be great!

A B C D E F G
62 1-Oct1,662,000 3,764,988 3,561,000 7,921,000 3,561,000 (44,532)63 2-Oct3,205,000 6,988,381 6,766,000 7,921,000 6,766,000 (34,629)64 3-Oct165,000 6,627,381 6,933,858 7,921,000 6,931,000 (41,600)65 6-Oct1,537,000 7,482,000 8,454,358 7,921,000 8,468,000 (39,875)66 7-Oct511,000 7,080,000 8,943,597 7,921,000 8,979,000 (41,441)67 8-Oct841,000 6,259,000 9,795,602 7,921,000 9,820,000 (33,580)

Below is my setup. In my workbook, there are two worksheets. There is a Summary worksheet and a Data worksheet. In column A of the Summary worksheet are dates. Column E contains a Match/Index formula for each date on column A (see worksheet)

The formula is

{=IF(ISERROR(INDEX(data!$E$3:$E$64,MATCH(1,IF(data!$A$3:$A$64=A15, IF(data!$E$3:$E$64"",1)),0))),"Null",INDEX(data!$E$3:$E$64,MATCH(1,IF(data!$A$3:$A$64=A15, IF(data!$E$3:$E$64"",1)),0)))}

Note: The above formula needs to be entered with Ctrl-Shift-Enter.

Column A of Data worksheet also contains dates but there are multiple instances of the date (see attach workbook). Column E of the Data worksheet contains various answers. Some fields are blank.

Since I have over several hundred entries for column A on the Summay worksheet, the above formula locks up my computer any time a value is changed.

What I want to accomplish:

1) Look at the date (one date at a time) on column A of the Summary worksheet. Need to loop throught all the dates.
2) With each date, compare it to the date on column A of the Data worksheet.
3) After a match is found move across to column E and reutrn the value of the cell
4) If the cell is empty, move to the next match date (since there are multiple entries of the same date) and return the value
5) If all the cells on column E are blank, then return the value "Null".

Is there an alternative non array formula?
I am using Excel 2003.
Can a macro perform the above task and not lock up my computer for hours?

Please help.

Thank you.

I am trying to build an automated 'year to date' column in a financial
summary worksheet but don't want to have to update the formula each month (ie
in month 6, sum cells A to F, then in month 7 change the formula to sum cells
A to G). Is there a function (or group of functions) which will 'lookup' the
current month e.g july and add up the contents of the cells in columns from A
to G (Jan to July)? The following month when I input August in a given cell
the function I'm looking for will recognise this and automatically add cells
A to H (Jan to Aug).

Hope this makes sense. Thanks in anticipation.

Hey,

I apologize for being an annoyance. There are hundreds of threads here that deserve more attention but I face a problem that has dodged/bugged me for a week. I've asked numerous co-workers and they have no idea to do this. I was hesistant to post this problem here but you guys seem to be the "real" geniuses.

Currently, I have a scorecard that pulls weekly period (for instance, P6W2), month-to-date (MTD), and year-to-date (YTD) sales and analytics data from other scorecards using a data validation list and the VLOOKUP, HLOOKUP functions. Once pulled, it calculates a YoY comparison. Since the 2010 period data is updated on a weekly basis on the other scorecards, it is very simple to calculate the 2010 MTD and YTD data since I just V/HLOOKUP the period sum or YTD sum from these scorecards. Using the data validation list, I choose a period (like P5 2010) and it gives me the 2010 MTD and YTD. It is a very easy "one-click" solution.

Unfortunately, since all the 2009 data is filled, I have yet to find a function that will sum up the MTD and YTD to the point listed in the monthly section. Currently, if I had P5W4 listed in my monthly section for both 2010 and 2009, the P5 2009 choice will list the sum of all the weeks in that period/month. So while I only need to the sum of P5W1 to P5W4, the P5 2009 choice will return the P5W1 to P5W5 sum. This is due to the HLOOKUP function only looking at the P5 2009 column.

However, in essence, I need a one-click solution using the current data validation setup that looks at the current weekly period choice (P6W2) and period (P6 2009). From these references, it needs to either sum the current choice and the previous weeks in that period OR subtract the remaining months in the period from the period total.

It's been a brain teaser for the last week. All my attempts have fallen flat. That's why I seek your guidance. If needed, I will post an dumbed-down example of the scorecard (since it is currently linked to files all over the server) if I receive any inquiries.

Thanks!

Hi,

I have a financial model on a SAP Business Warehouse Query, which i derive via the SAP BW Business Explorer in Excel (named BEx). I derive the data from the query via the getpivotdata formula in combination with ranged names:

For example

In the Month : = getpivotdata(Pivot,concatenate("Philips Design"," ","EBIT PD"," ","Actuals"," ","Month"," ")
In the Quarter := getpivotdata(Pivot,concatenate("Philips Design"," ","EBIT PD"," ","Actuals"," ","Quarter"," ")
Full Year : = getpivotdata(Pivot,concatenate("Philips Design"," ","EBIT PD"," ","Actuals"," ")

whereas Actuals/Month/Quarter are ranged names. For Month and Quarter i set the applicable month or Quarter that applies to the current reporting, eg. 012.2007 as month and 20074 as quarter.

My concrete question is for the getpivotdata formula to derive the Year to Date figure. The month figures in the pivot table are in the month data.

Ps: i would like to keep all formulas to this one pivot table, i know i can do it with a second one.

greetings,

Jack

Good day everyone!!
I would really appreciate some help with a formula that can sum up Costs over multiple rows and columns based on the year to date. I attached a sample of the data I'm trying to work on.

I'm trying to do an analysis on a quarterly basis, e.g. Jan vs Mar.
For Jan vs Mar, I chose Mar in the drop down box and the Sales - Mar YTD will be shown in the Total column. For the Jan part, I used the formula "=SUM(D14:CHOOSE(E16,D14,E14,F14,G14,H14,I14,J14,K14,L14,M14,N14,O14))" with E16 showing "1" for the month of Jan.
As Sales is only on 1 row (Row 14), my colleague helped me with the above formula.

For Costs (which could be 4 rows or more), the Costs - Mar YTD is also shown in the Total column. However, for the Jan part (highlighted in red), I'm stumped. I could repeat a variation of "=SUM(D14:CHOOSE(E16,D14,E14,F14,G14,H14,I14,J14,K14,L14,M14,N14,O14))" 4 times, but is there any simpler way to do this? I'd like the option of switching up the months for analysis so a simple formula would really help.

Apologies if my question is "wordy" but I could really use some help as I'm not very Excel-savvy!!

Hi,

First some back ground:

I have data which I import from CSV files from a JDE application on a monthly basis, using "JobCostData" macro ( have not posted code here as it is contained in the attached file).

For each month I import the data my macro creates a seperate spreadsheet and names the spreadsheet as the "mmm-yy" the data relates to, and then takes the information from each sheet and collates this on to on spreadsheet named "Pivot Data".

I then run a second macro "MacroXX", ( again have not posted code here as it is contained in the attached file), this macro looks at the data on "Pivot Data" and creates a unique list of Work Order Numbers and Descriptions and copies them to the spreadsheet named "Report".

Now what I would like to do (and can't get my head around):

Once the "Report" has been populated I would like to come up with a series of total "Cost $" for each Work Order by:
1. Month, for the past 6 months based on a Calendar Month End date (Note: For now I have hard coded the dates, but I will add code at a later stage to prompt user to select a date which will then write this to cell H4 on the "Report" spreadsheet and have the other dates derived from this date.)
2. Year to Date (YTD) based on the Financial year the Calendar Month End Date falls
3. Inception to Date (ITD) based on the

Note:
1. I have tried using Pivot Tables but this will not give me ITD totals.
2. I would prefer to use a macro rather than formulas.
3. I have populated the "Report" spreadsheet with the expect result based on the current data.

Any help or suggestions would be much appreciated.

Cheers,

Gizzmo

essentially, i'm making a paystub for an employee.

i need a specific cell to give me a year to date total from a specific date or pay period.

for example: for a wage of $1.00 an hour.

if an employee works a standard two week period @ 40 hrs he is to be paid $80.00 dollars on the cell next to it, i need it to give me a year to date total for that specific payperiod, but also in payperiods. ( for it to calculate how many payperiods from the beginning of the year until that specific date and multiply by the wage.

tried every formula i know of but dates are somewhat confusing.

any help would be greatly appreciated.

thanks,
ulises

I previously asked for help with this:

In Excel2000, I would like to create a dynamic year-to-date formula. I
have the following data starting in row 3 and going from column A to
column X:

Jan Actuals Jan Budget Feb Actuals Feb Budget
100 200 300 400

I would like to use cell A1 as an input cell for the month ( ex. Feb =
2). I would like the formula to lookup the month and then tell me how
much Feb YTD Actuals are ( in this example = 400 ). All I want to
change each month is the input cell in A1 and have the formula
automatically give me the results.

Someone responded with this formula:

=SUMPRODUCT(--(MOD(COLUMN(OFFSET($A$2,,,,CHOOSE($A$10,1,3,5,7,9, 11,13,15,17,19,21,23))),2)=1),OFFSET($A$2,,,,CHOOS E($A$10,1,3,5,7,9,11,13,15,17,19,21,23)))

Can someone plaes explain this formula. It works perfectly but I don't
understand it.

Thanks

For example I have $500 for June operating expense. How do i annulaize
it? Or how do I estimate year to date expense.
Thanks

Hi All
I have a formula that gives me year-to-date on an incomplete
range A to L..

=SUM(OFFSET(A2:AL,0,0,1,MATCH(M1,A1:L1,0)))

So incoming data
A3 = 3
B3 = 4
C3 = 5

etc....

To go with is I have targets completed to L

A2 = 3
B2 = 3
C2 = 3
etc

And months (working on a July - June year)

A1 = Jul
B1 = Aug
C1 = Sep
etc

Problems

We are now in October so we have figures to date
But I also want the targets added up to date not the whole year
which the above formula does.

1) I would like the formula to work from todays date eg NOW() but match
cant seem to handle date types (cell format = mmm)

2) formula works fine on incomplete rows but in the case of target
takes the whole row.

So is there another formula that
a) allows for date comparisons given the formula above
b) copes with full rows (so I only want Jul to October added in the
above senario)

Cheer

Phil

Hi. I am having trouble developing a SUMPRODUCT/INDEX/MATCH formula for the attached worksheet. I have read multiple posts on the subject and have tried to modify the answers given, but I am still unable to get the formula right. I hope someone can help.

In the attached spreadsheet I have several SUMIF formulas in cells K2:L6. These work, but I need to try and achieve the same result without the SUMIF formula. From reading posts I can see this can be done with a combination of SUMPRODUCT, INDEX and MATCH. Can someone review this and tell me how to sum up the TOTAL and POSITIVE columns for the different teams using SUMPRODUCT, INDEX and MATCH.

Thanks very much

Hi guys,

I'm just trying to calculate YTD (year-to-date) returns on a timeseries. Please see the excel file - Column A has the dates, Column B -the values.
I've manually calculated the returns for 2009 and 2010, but I need to get a formula or vba code that will do this.
A code that can detect the MOst RECENT date and divide the corresponding Column B value by the previous year end value (ie 31 Dec) to compute the return.

Any suggestions?

thanks

Hi there,

I have a multi-tab spreadsheet that I'd like to summarise, am trying to use index/match, but this may not be the best option.

I could use a vlookup to pull in by column but sometimes the format of these sheets are not uniform, so pulling in by column is not ideal, rather to index by date (across top) and description (left).

But I can't figure out how to do a index/match for all tabs to pull into SubTot.

Suggestions welcome.

Many thanks, Maddy

Hi guys,

I have an issue trying to automate a financial year to date calculation. For the life of me I cannot figure it out!

I am trying to create an automated calcualtion whereby I can just copy the formula down and the year to date return is calculated.

I have monthly index values which make it easy to create 12 month returns as you can see in my attachment in column i. You simply drag the formula down for the next month and the 12 month return upto that next month is calculated.

However, the financial year to date (FYTD) calc is more complicated. It has to use the June index value as the base month for next 12 calcs, and then move to the next June index value as the base for the 12 calcs after that. I have done this manually for each month in column J.

I know i have rambled but I was hoping you could suggest a way to automate this calc?? I have been on this for days! Just cant figure it.

Help would be much appreciated.

Thanks.

Guys,

I have a graph which is populated by year to date and started last jan-11 up to dec-11.
right now i will update this graph for jan-12. how should looks the data to be displayed..

Here is my sample: current:

jan-11|feb-11|mar-11|apr-11|may-11|Jun-11|jul-11|aug-11|sep-11|oct-11|nov-11|dec-11

now i will update my graph for 2012. it should be the Jan-12 to be in the first column or in the last column after dec-11

jan-12|feb-11|mar-11|apr-11|may-11|Jun-11|jul-11|aug-11|sep-11|oct-11|nov-11|dec-11 or here Jan-12

Thanks in advance...

JOV

Dear All,

May I request for your kind assistance in helping me with below logic, excel sheet of current formula is likewise attached in this thread for your viewing.

Data Available are as follows:
2010 Monthly Sales
2011 Monthly Sales
2011 Monthly Target

Compare 2011 Monthly Year to Date sales with Monthly 2010 Year to Date Sales.
(i.e if Data available for 2011 is from Jan-May which is equivalent to 5,0000, 2011 data will be compared vs 2010 Jan-May sales only.)

As you ad data in 2011 the range of computation will grow as well
(i.e if data is Jan-June 2011 comparison will be Jan-June 2010)

I have a formula that I use but since the data are interlinked to other sheets I get error because it now compares total 2010 sales vs 2011 year to date sales.
(i.e 2010 sales = 20,000,000 while 2011 Ytd May is only 5,000,000 instead of computing YTD 2010 vs YTD 2011 it compares 20,000,000 vs 5,000,000)

Hope you can help me on this, really looking forward to your help.

Thank you!

Regards,

francis

I'm trying to find a formula that will give me a count of all programs by month that have an evaluation result.
So, in column A1:A24 I have a list of programs with. In cell A25, I have the total number of programs. In column B1:B24, only 7 of those programs have evaluation results, some of the rows are blank.

I know that I can get a sum of the programs by month, but how do I only get a count of those with an evaluation result? What formula can be used to get a year to date (a running count), not a count based on 12 months? Meaning that we would need a year to date for each month, so currently for October to June (based on fiscal). If we based it on year to date, then I think we would have to divide be 12 and that would make the results inaccurate.

Can someone please help with this? Please let me know if I need to clarify.

Thanks.

I have a workbook with 12 sheets for monthly results and a Year to Date sheet. For presentation reasons, the monthly results are listed as seen in the first three worksheets, but I want the YTD results to be structured as seen in the fourth worksheet. I have shown how I am currently achieving this in the "Call" section of the YTD sheet, but as there are 12 months and many more variables in the original workbook, I'm wondering if there is any easier way than the one I'm using.

Thanks in advance,

Kevin

I am trying to calculate year to date, base on percentage for example: person x on Jan 95% on Feb. 95 % and so for, I would like the total to be as I input the info on the cell, for example since we are in April as in last month person x has in Jan 95%the same for Feb. and the same in march it should give me a year to date of 95% how can I do this with out recalculating it every month.

I apologize if I'm not very clear with what I'm asking and I will appreciate any help
Thank you

month____jan____ feb ____mar____apr____may____...___year-to-date total
goal_____200____300____ 400____100____ 300___ ... _________________?
actual___ 230____340___________________________________________570

I want a year-to-date formula (for "?" above) that will sum only the values in the 2nd row ("goal") that have a corresponding "actual" value. the idea is to not show year-end total, but year-to-date. and a simple sum() formula won't do the trick.

any thoughts?


No luck finding an answer? You could always try Google.