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

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

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

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