Need Year-To-Date Sum Function with nested Vlookup and offset

[Nena G]

I am using the below function in excel to get a ytd total for items that I
have on a report and it is not working. How do you get YTD totals to work
when you need to be able to select the period number and have the formula sum
the columns for selected rows, the data that I need to use for each YTD
column exists on a separate tab in the workbook (i.e. one tab called TOTAL
DC& HR1, one tab called SC, etc...). I need to sum up their individual
periods based on the current period (i.e. if its period 4 then I need to sum
up periods 1-4 for each tab on this one sheet). Is there a way to do this
with out creating a running YTD tab for each by period tab?
=SUM(OFFSET(VLOOKUP($B6,'TOTAL DC &
HR1'!$B3:$Q3,$C$2+1,FALSE),0,0,1,MONTH($C$1-1)))

My data Is as follows

A B C D E
1 Total Total XHR SC
6 Not used Sales $600 $600 $231
7 Cartons 125 125 58
8 Shipped 358 358 85

 

[David]

Difficult to exactly understand how you have the various tables- from the
sample you gave I presume the months are in numeric format- you could do this
1 Have a cell somewhere with the month up to number you want say in c2
2 Then beside every line have an If statement e.g =IF(B4<=$C$2,"OK","Next
period") to determine if that month number is in range
The a sumif to add all numbers that have OK accross from them e.g
=SUMIF(A4:A10,A11,C4:C8)
where Col A is where ae:a10 is where the If formula is- A11 has OK in text
format & C4:C8 is the nymber range

You can then just directly link the total to you summary sheet- by changeing
c2 your YTD will also change

 

[Nena G]

David: Thank you for your help but the data is not set up in a way that this
formula works, I wish that I could put a screen print of my data with this
post, to show you


I need to use a vlookup to match the data catagory and my periods are in
text month format. and the data that I'm summing is on several different
tabs, thats why I'm in need of vlookups. Its not Ideal but I can't change
the way the data is setup because they use these sheets for reports.


In text format this is my YTD Report sheet
Total DC/HR Total DC Sct Ard
SB Ind Ful Ala Zan Jon
Mar FIC Con HR
Totals with HR YTD YTD YTD
YTD YTD YTD YTD YTD
YTD YTD YTD YTD YTD YTD
2009 Sales " 903,089 " " 903,089 " " 113,761 " " 127,253
" " 85,503 " " 92,674 " " 109,365 " " 90,884 " " 122,919 " " 80,946 " "
79,784 " " 903,089 " " 903,089 " " 903,089 "
Sub Total Fixed Costs: " 6,809 " " 6,809 " 394 679
504 717 951 661 634 596 561
2 " 1,109 " -
Sub Total Semi-Variable: " 2,163 " " 2,095 " 273 228
197 278 154 236 234 194 229
60 12 67
Sub Total Variable Costs: " 12,553 " " 12,409 " " 1,428 " " 1,938 " "
1,193 " " 1,388 " " 1,473 " " 1,326 " " 1,513 " 985 893
58 217 144
Total Costs " 21,525 " " 21,313 " " 2,095 " " 2,845 " "
1,894 " " 2,383 " " 2,577 " " 2,222 " " 2,382 " " 1,775 " " 1,683 " 120
" 1,337 " 211
% to Sales 2.38% 2.36% 1.84% 2.24%
2.22% 2.57% 2.36% 2.45% 1.94%
2.19% 2.11% 0.01% 0.15% 0.02%
% to Shipments 2.84% 2.81% 2.30%
2.74% 2.61% 2.90% 2.80% 2.92%
2.30% 2.54% 2.48% 0.00% 0.00%
0.00%

Purchases " 738,756 " " 738,756 " " 82,555 " "
97,218 " " 66,869 " " 77,824 " " 84,386 " " 68,230 " " 93,173 " " 64,545 " "
63,956 " " 39,998 " - -
Shipments " 758,753 " " 758,753 " " 91,088 " " 103,901 " "
72,493 " " 82,186 " " 91,959 " " 76,030 " " 103,425 " " 69,900 " " 67,771 "
- - -
Cartons Rec'd " 33,534 " " 33,534 " " 3,970 " " 4,543 " "
3,304 " " 3,633 " " 4,050 " " 3,372 " " 4,442 " " 3,218 " " 3,002 " 771
- -
Cartons Shipped " 33,301 " " 33,301 " " 4,060 " " 4,496 " "
3,227 " " 3,609 " " 3,965 " " 3,455 " " 4,514 " " 3,039 " " 2,935 " 770
- -

Text format of one of hte Source data sheets
Totals with HR Feb Mar Apr QTR
1 May Jun Jul QTR 2 Aug
Sep Oct QTR 3 Nov Dec
Jan QTR 4 Total
2009 Sales " 854,289 " " 1,053,039 " " 872,340 " " 2,779,668 " " 903,089 " "
1,152,120 " " 861,566 " " 2,916,775 " " 855,899 " " 1,073,860 " " 886,890 " "
2,816,649 " " 947,195 " " 1,314,054 " " 816,808 " " 3,078,057 " " 11,591,149 "
Sub Total Fixed Costs: " 6,656 " " 7,143 " " 6,771 " " 20,569 " " 6,809
" " 7,158 " " 6,769 " " 20,736 " " 6,961 " " 7,108 " " 7,059 " " 21,128 " "
6,940 " " 7,053 " " 6,749 " " 20,742 " " 83,176 "
Sub Total Semi-Variable: " 1,701 " " 2,378 " " 1,463 " " 5,542 " " 2,163
" " 3,376 " " 3,154 " " 8,693 " " 3,712 " " 3,511 " " 3,004 " " 10,226 " "
2,700 " " 3,082 " " 2,863 " " 8,645 " " 33,106 "
Sub Total Variable Costs: " 13,432 " " 14,274 " " 11,951 " " 39,658 " "
12,553 " " 15,638 " " 12,300 " " 40,491 " " 12,305 " " 16,011 " " 12,494 " "
40,809 " " 12,797 " " 16,438 " " 12,374 " " 41,610 " " 162,567 "
Total Costs " 21,790 " " 23,795 " " 20,185 " " 65,770 " " 21,525 " " 26,172
" " 22,223 " " 69,920 " " 22,977 " " 26,630 " " 22,557 " " 72,163 " " 22,437
" " 26,573 " " 21,987 " " 70,997 " " 278,849 "
% to
Sales 2.55% 2.26% 2.31% 2.37% 2.38% 2.27% 2.58% 2.40% 2.68% 2.48% 2.54% 2.56% 2.37% 2.02% 2.69% 2.31% 2.41%
% to
Shipments 2.42% 2.44% 2.66% 2.50% 2.84% 2.67% 2.64% 2.71% 2.83% 2.32% 2.62% 2.56% 3.06% 2.88% 2.94% 2.96% 2.67%
4
Purchases " 939,740 " " 975,369 " " 723,137 " " 2,638,245 " " 738,756 " "
993,626 " " 877,088 " " 2,609,469 " " 936,202 " " 1,125,118 " " 735,503 " "
2,796,823 " " 697,791 " " 977,570 " " 743,266 " " 2,418,627 " " 10,463,165 "
Shipments " 899,420 " " 974,155 " " 759,558 " " 2,633,134 " " 758,753 " "
980,018 " " 841,211 " " 2,579,982 " " 813,182 " " 1,149,519 " " 861,179 " "
2,823,880 " " 733,474 " " 922,093 " " 746,760 " " 2,402,326 " " 10,439,323 "
Cartons Rec'd " 34,894 " " 42,684 " " 32,584 " " 110,161 " " 33,534 " "
43,273 " " 35,238 " " 112,045 " " 34,962 " " 43,417 " " 31,223 " " 109,602
" " 29,661 " " 39,819 " " 30,635 " " 100,114 " " 431,922 "
Cartons Shipped " 33,803 " " 38,696 " " 33,624 " " 106,123 " " 33,301 " "
42,735 " " 33,196 " " 109,232 " " 31,487 " " 44,761 " " 34,106 " " 110,354
" " 30,357 " " 39,344 " " 32,777 " " 102,479 " " 428,187 "

 

[David]

if you want to send the spread sheet with sample data I'll have a look at it
for you (e-mail address removed)

David