Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf statement: incrementing
I have the following formula:
=SUMIF('weekly dates'!A1:AZ1,""&A5,Details!L7:BK7) Weekly dates is a static sheet, &A5 is the current date and Details!L7:BK7 are cells that are calculated weekly on a sheet different from where this formula is used. There are 4 weeks per period and this report is done weekly. I need the SUMIF to be just as it is for week 1, but for week 2, I need to include weeks 1&2, week 3 to include 1-3 and week 4 to include 1-4. Then it starts over after week 4. I can do by changing the formula weekly in the following way. =SUMIF('weekly dates'!A1:AZ1,""&A5-8,Details!L7:BK7) week2 =SUMIF('weekly dates'!A1:AZ1,""&A5-16,Details!L7:BK7) week3 =SUMIF('weekly dates'!A1:AZ1,""&A5-24,Details!L7:BK7) week4 I know there has to be a cleaner and more correct way of doing this. Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf statement: incrementing
I put dates in column G and numbers to sum in column H
In A5 I have the "current date" I used this formula =SUMIF(G1:G31,""&A5-IF(DAY(A5)<8,8,IF(DAY(A5)<14,16,24)),H1:H31) to try to duplicate your three options. DAY(A5) returns the calendar day and I check if was less than 14, less than 14 to compute how many days to subtract from A5. You will need to work on it - 8 days in a week had me confused best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Kesbutler" wrote in message ... I have the following formula: =SUMIF('weekly dates'!A1:AZ1,""&A5,Details!L7:BK7) Weekly dates is a static sheet, &A5 is the current date and Details!L7:BK7 are cells that are calculated weekly on a sheet different from where this formula is used. There are 4 weeks per period and this report is done weekly. I need the SUMIF to be just as it is for week 1, but for week 2, I need to include weeks 1&2, week 3 to include 1-3 and week 4 to include 1-4. Then it starts over after week 4. I can do by changing the formula weekly in the following way. =SUMIF('weekly dates'!A1:AZ1,""&A5-8,Details!L7:BK7) week2 =SUMIF('weekly dates'!A1:AZ1,""&A5-16,Details!L7:BK7) week3 =SUMIF('weekly dates'!A1:AZ1,""&A5-24,Details!L7:BK7) week4 I know there has to be a cleaner and more correct way of doing this. Any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf statement: incrementing
Thanks but this isn't exactly what I need. I need the formula to only
calculate the weeks in that current period. Your formula grabs the weeks previous based on the date. Each period has 4 weeks and I need to track each week within each period and increment as the period progresses. Week 1 = only calculates week 1 Week 2 = calculates week 1 & week2 Week 3 = calculates weeks 1,2 and 3 Week 4 = calcualtes weeks 1,2,3 and 4. Then after week 4 it would start over again with week 1 as it would be a new period. I guess I'll keep plugging away until I get it figured out. Thanks "Bernard Liengme" wrote: I put dates in column G and numbers to sum in column H In A5 I have the "current date" I used this formula =SUMIF(G1:G31,""&A5-IF(DAY(A5)<8,8,IF(DAY(A5)<14,16,24)),H1:H31) to try to duplicate your three options. DAY(A5) returns the calendar day and I check if was less than 14, less than 14 to compute how many days to subtract from A5. You will need to work on it - 8 days in a week had me confused best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Kesbutler" wrote in message ... I have the following formula: =SUMIF('weekly dates'!A1:AZ1,""&A5,Details!L7:BK7) Weekly dates is a static sheet, &A5 is the current date and Details!L7:BK7 are cells that are calculated weekly on a sheet different from where this formula is used. There are 4 weeks per period and this report is done weekly. I need the SUMIF to be just as it is for week 1, but for week 2, I need to include weeks 1&2, week 3 to include 1-3 and week 4 to include 1-4. Then it starts over after week 4. I can do by changing the formula weekly in the following way. =SUMIF('weekly dates'!A1:AZ1,""&A5-8,Details!L7:BK7) week2 =SUMIF('weekly dates'!A1:AZ1,""&A5-16,Details!L7:BK7) week3 =SUMIF('weekly dates'!A1:AZ1,""&A5-24,Details!L7:BK7) week4 I know there has to be a cleaner and more correct way of doing this. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF statement with AND statement | Excel Discussion (Misc queries) | |||
sumif statement | Excel Discussion (Misc queries) | |||
Sumif statement | Excel Discussion (Misc queries) | |||
Help please, IF statement/SUMIF statement | Excel Worksheet Functions | |||
sumif statement | Excel Worksheet Functions |