| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding only part of a column of data
I have a weekly report that I send out to our team. I gather data for the
current year each month and use vlookup for most of the report. However, I need to report some of the numbers from last year as well. I have the weeks numbered consecutively in column A. On my report I enter the current week number to gather the data from the vlookup. How can I get it to give me a year to date total from the worksheet from last year? Example: I am currently reporting week 8 numbers on my report. I can get my current YTD number, but I need to know how to get my LY numbers for week 1-8. This will need to change as I change the week number on my report. Does this make sense? -- keith jones |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding only part of a column of data
SUMPRODUCT might work for you. Something like:
=SUMPRODUCT(--(A1:A100=1),--(A1:A100<=8),B1:B100) This will add all the cells in Column B with corresponding values in Column A that are between the values 1 and 8. Adjust the cell references to meet your needs. HTH Elkar "kajones" wrote: I have a weekly report that I send out to our team. I gather data for the current year each month and use vlookup for most of the report. However, I need to report some of the numbers from last year as well. I have the weeks numbered consecutively in column A. On my report I enter the current week number to gather the data from the vlookup. How can I get it to give me a year to date total from the worksheet from last year? Example: I am currently reporting week 8 numbers on my report. I can get my current YTD number, but I need to know how to get my LY numbers for week 1-8. This will need to change as I change the week number on my report. Does this make sense? -- keith jones |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding only part of a column of data
Here is the idea...
Suppose you want to SUM the numbers in Col A from A1 to say A10 then enter 10 in B1 and this formula in C1 =SUM(INDIRECT("A1:A"&B1)) Now if you change B1 to 20 it will give you sum of A1:A20... You can adapt this to your requirement... basically build a string (using strings and references) giving you the range to SUM, pass it to INDIRECT and then put a SUM around Indirect... "kajones" wrote: I have a weekly report that I send out to our team. I gather data for the current year each month and use vlookup for most of the report. However, I need to report some of the numbers from last year as well. I have the weeks numbered consecutively in column A. On my report I enter the current week number to gather the data from the vlookup. How can I get it to give me a year to date total from the worksheet from last year? Example: I am currently reporting week 8 numbers on my report. I can get my current YTD number, but I need to know how to get my LY numbers for week 1-8. This will need to change as I change the week number on my report. Does this make sense? -- keith jones |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding only part of a column of data
That did it. Thanks,
-- keith jones "Sheeloo" wrote: Here is the idea... Suppose you want to SUM the numbers in Col A from A1 to say A10 then enter 10 in B1 and this formula in C1 =SUM(INDIRECT("A1:A"&B1)) Now if you change B1 to 20 it will give you sum of A1:A20... You can adapt this to your requirement... basically build a string (using strings and references) giving you the range to SUM, pass it to INDIRECT and then put a SUM around Indirect... "kajones" wrote: I have a weekly report that I send out to our team. I gather data for the current year each month and use vlookup for most of the report. However, I need to report some of the numbers from last year as well. I have the weeks numbered consecutively in column A. On my report I enter the current week number to gather the data from the vlookup. How can I get it to give me a year to date total from the worksheet from last year? Example: I am currently reporting week 8 numbers on my report. I can get my current YTD number, but I need to know how to get my LY numbers for week 1-8. This will need to change as I change the week number on my report. Does this make sense? -- keith jones |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding only part of a column | Excel Worksheet Functions | |||
| Adding data to existing data that has a unique number in column | Excel Discussion (Misc queries) | |||
| Adding a letter to the beginning of each part number of a column | Excel Discussion (Misc queries) | |||
| Copying Part of a row down part of a column | Excel Discussion (Misc queries) | |||
| Adding column data | Excel Worksheet Functions | |||
Linear Mode
