Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I convert data with fields in rows to fields as columns | Excel Discussion (Misc queries) | |||
Creating a number from different fields. | Excel Worksheet Functions | |||
number of fields in the row fields in pivot table | Excel Discussion (Misc queries) | |||
Need pie chart with number of fields instead of info in fields | Charts and Charting in Excel | |||
Addition of a number of fields | Excel Discussion (Misc queries) |