sum x number of fields containing data
 

I'm trying to make a spreadsheet that compares month to date sales figures to
previous years. How can I reference a field that says how many business days
have passed in the current month, and have THAT number of fields, containing
data, total from another page? i.e. -- If this is the 6th business day of
the month, how can I have the sheet to total the first 6 fields that have
data from last year? (The history sheet contains blank fields for days of
the month that were not business days.)

sum x number of fields containing data
 

Hi Tammy,

Assuming you have multiple prior years' dates in A1:A5000 of a sheet named 'PriorYears', with the values in the corresponding cells
in Column B, this year's dates are in ColumnA of your worksheet of interest, then the following array formula will do the job:
=SUM(IF((YEAR(PriorYears!A$1:A$5000)+1=YEAR(A1))*( MONTH(PriorYears!A$1:A$5000)=MONTH(A1))*(DAY(Prior Years!A$1:A$5000)<=DAY(A1)),PriorYears!B$1:B$5000) )

If you only have last year's data in a sheet named 'LastYear', then the following array formula will do the job:
=SUM(IF((MONTH(LastYear!A$1:A$366)=MONTH(A1))*(DAY (LastYear!A$1:A$366)<=DAY(A1)),LastYear!B$1:B$366) )

Insert whichever version of the formula you need on your first data row, change the worksheet name ('PriorYears'/'LastYear') and the
range references to suit your needs (eg you'll need to have the formula start at row 2 if row 1 is a header row), then copy down as
far as needed.

Note: As these are array formulae, you'll need to press 'Ctrl-Shift-Enter' when you input them, instead of just 'Enter'.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"Tammy" wrote in message ...
I'm trying to make a spreadsheet that compares month to date sales figures to
previous years. How can I reference a field that says how many business days
have passed in the current month, and have THAT number of fields, containing
data, total from another page? i.e. -- If this is the 6th business day of
the month, how can I have the sheet to total the first 6 fields that have
data from last year? (The history sheet contains blank fields for days of
the month that were not business days.)

sum x number of fields containing data
 

Thank you for the reply. Please excuse my ignorance, but I've never used an
array formula before. The formula you gave me would accomplish something
more complicated than what I'm attempting. I tried to alter it to fit my
needs, but I'm still doing something wrong. Here's a little better
description of what I'm attempting -- I have a sheet (Store Totals) which
includes field (D4), indicating how many business days have passed during the
current month. I have another sheet (Historical Data) which has column B
with days of the month, and column C with sales data for last year. The
entire workbook is for one month - comparing this year November MTD to last
year November MTD. If you would be so kind as to use this information to
provide the appropriate formula, I would be greatly appreciative.

Thanks.




"macropod" wrote:

Hi Tammy,

Assuming you have multiple prior years' dates in A1:A5000 of a sheet named 'PriorYears', with the values in the corresponding cells
in Column B, this year's dates are in ColumnA of your worksheet of interest, then the following array formula will do the job:
=SUM(IF((YEAR(PriorYears!A$1:A$5000)+1=YEAR(A1))*( MONTH(PriorYears!A$1:A$5000)=MONTH(A1))*(DAY(Prior Years!A$1:A$5000)<=DAY(A1)),PriorYears!B$1:B$5000) )

If you only have last year's data in a sheet named 'LastYear', then the following array formula will do the job:
=SUM(IF((MONTH(LastYear!A$1:A$366)=MONTH(A1))*(DAY (LastYear!A$1:A$366)<=DAY(A1)),LastYear!B$1:B$366) )

Insert whichever version of the formula you need on your first data row, change the worksheet name ('PriorYears'/'LastYear') and the
range references to suit your needs (eg you'll need to have the formula start at row 2 if row 1 is a header row), then copy down as
far as needed.

Note: As these are array formulae, you'll need to press 'Ctrl-Shift-Enter' when you input them, instead of just 'Enter'.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"Tammy" wrote in message ...
I'm trying to make a spreadsheet that compares month to date sales figures to
previous years. How can I reference a field that says how many business days
have passed in the current month, and have THAT number of fields, containing
data, total from another page? i.e. -- If this is the 6th business day of
the month, how can I have the sheet to total the first 6 fields that have
data from last year? (The history sheet contains blank fields for days of
the month that were not business days.)

sum x number of fields containing data
 

Hi Tammy,

Given that the 'entire workbook is for one month', I take it that both worksheets only have one month's data each. in that case, the
following array formula, pasted anywhere on your 'Store Totals' worksheet should do the trick:
=SUM('Historical Data'!$C$2:OFFSET('Historical Data'!$C$2,$D$4+SUM(IF(MOD('Historical Data'!$B$2:OFFSET('Historical
Data'!$B$2,INT($D$4/5)*2+$D$4,),7)<2,1,))-1,))

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"Tammy" wrote in message ...
Thank you for the reply. Please excuse my ignorance, but I've never used an
array formula before. The formula you gave me would accomplish something
more complicated than what I'm attempting. I tried to alter it to fit my
needs, but I'm still doing something wrong. Here's a little better
description of what I'm attempting -- I have a sheet (Store Totals) which
includes field (D4), indicating how many business days have passed during the
current month. I have another sheet (Historical Data) which has column B
with days of the month, and column C with sales data for last year. The
entire workbook is for one month - comparing this year November MTD to last
year November MTD. If you would be so kind as to use this information to
provide the appropriate formula, I would be greatly appreciative.

Thanks.




"macropod" wrote:

Hi Tammy,

Assuming you have multiple prior years' dates in A1:A5000 of a sheet named 'PriorYears', with the values in the corresponding
cells
in Column B, this year's dates are in ColumnA of your worksheet of interest, then the following array formula will do the job:
=SUM(IF((YEAR(PriorYears!A$1:A$5000)+1=YEAR(A1))*( MONTH(PriorYears!A$1:A$5000)=MONTH(A1))*(DAY(Prior Years!A$1:A$5000)<=DAY(A1)),PriorYears!B$1:B$5000) )

If you only have last year's data in a sheet named 'LastYear', then the following array formula will do the job:
=SUM(IF((MONTH(LastYear!A$1:A$366)=MONTH(A1))*(DAY (LastYear!A$1:A$366)<=DAY(A1)),LastYear!B$1:B$366) )

Insert whichever version of the formula you need on your first data row, change the worksheet name ('PriorYears'/'LastYear') and
the
range references to suit your needs (eg you'll need to have the formula start at row 2 if row 1 is a header row), then copy down
as
far as needed.

Note: As these are array formulae, you'll need to press 'Ctrl-Shift-Enter' when you input them, instead of just 'Enter'.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"Tammy" wrote in message ...
I'm trying to make a spreadsheet that compares month to date sales figures to
previous years. How can I reference a field that says how many business days
have passed in the current month, and have THAT number of fields, containing
data, total from another page? i.e. -- If this is the 6th business day of
the month, how can I have the sheet to total the first 6 fields that have
data from last year? (The history sheet contains blank fields for days of
the month that were not business days.)