Quarterly and YTD calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I need to get a formula to add up quarterly data. Right now I put the month i want into E4 and the Line I am looking for in E3. I want a formula to add the entire quarter based on the date in E4. I also need this Year to date
HLOOKUP($E$4,'0110-Budget'!$B$8:$N$100,E3,FALSE),0

TI

Todd
 
Hi
what value is exactly in E4. also define which columns you want to add.
I think a SUMPRODUCT formula will do but you may also consider using a
pivot table for this

--
Regards
Frank Kabel
Frankfurt, Germany

Todd said:
Hi, I need to get a formula to add up quarterly data. Right now I
Click to expand...
put the month i want into E4 and the Line I am looking for in E3. I
want a formula to add the entire quarter based on the date in E4. I
also need this Year to date.
 
Thanks Frank

E$4 contains the name of the month I am searching for. The data is on a different worksheet. There are 18 different worksheets that I am pulling information from for this table.

Jan. Feb. March April May June July Aug. Sept Oct. Nov. Dec.
22 23 24 25 26 27 28 29 30 31 32 3

So, if I put Feb. in e4 I would get 22+23+24 returned for the quarterly data formula and 22+23 for the Year to date formul


Todd
 
Hi Todd
YTD is simple. For this use
=SUM(OFFSET('sheet1'!$A$2,0,0,MATCH(E4,'sheet1'!$A$1:$L$1,0)))

Your quarterly figures are more complicates as you don't use real date
values as headings. One way:
1. Create a lookup table on a separate sheet (e.g. called lookup):
A B
1 Jan. March
2 Feb. March
3 March March
4 April June
.....
this lookup table matches your month names to the end of quarter month
2. Now use the formula
=SUM(OFFSET('sheet1'!$A$2,0,0,MATCH(VLOOKUP(E4,'lookup'!$A$1:$B$12,2,0)
,'sheet1'!$A$1:$L$1,0)))


--
Regards
Frank Kabel
Frankfurt, Germany

Todd said:
Thanks Frank,


E$4 contains the name of the month I am searching for. The data is
Click to expand...
on a different worksheet. There are 18 different worksheets that I am
pulling information from for this table.
Jan. Feb. March April May June July Aug. Sept Oct. Nov. Dec.
22 23 24 25 26 27 28 29 30 31 32 33

So, if I put Feb. in e4 I would get 22+23+24 returned for the
Click to expand...
quarterly data formula and 22+23 for the Year to date formula
 
Frank, this is be simple but I am having a hard time with it

January February March April May YTD Month to calculate
Toys 1 2 3 4 5
Cars 7 2 2 3 5

=SUM(OFFSET(a1,0,0,MATCH(f1,A1:L1,0),0)

I can't get this to work. I have got it to sum all of columns by the number of the month I use (April = sum 4 rows in January) but not to sum 4 months of toys (columns

Thanks again. You are a great hel

Tod
 
Got it. I have been playing with this and I have it working. I moved the lookup date into O1 and am using a new clean workbook so its a little different. More questions if you will. Both of these seem to do the same thing. Is the match neccesary? And I tried to add the today() funtion to the match but get #N

=SUM(OFFSET(A2,,,,MATCH(O1,A1:$L$1,0))

=SUM(OFFSET($A2,,,,MONTH(TODAY()))

=SUM(OFFSET(A2,,,,MATCH(MONTH(TODAY()),A1:$L$1,0))) = #NA
 
Hi
problem is, the MONTH function returns a value of 1-12 but you have
text values like 'Jan' in your heading row

--
Regards
Frank Kabel
Frankfurt, Germany

Todd said:
Got it. I have been playing with this and I have it working. I
Click to expand...
moved the lookup date into O1 and am using a new clean workbook so its
a little different. More questions if you will. Both of these seem to
do the same thing. Is the match neccesary? And I tried to add the
today() funtion to the match but get #NA
 
Back
Top