| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match week, count rows, sum rows?
Hi,
I've been westling with this and making no progress. I have columns Date, Subject, # figure. On another sheet I will have a reference date which is a week ending date for each week. I need to find all rows from the 1st sheet whose date falls in the same week as my week ending reference date, count the number of rows found, and sum the #figure for all rows found. Example: Reference date: = 5-Jan-07 A B C Date Subject # Figure 1-Jan-07 abc 3 4-Feb-07 def 1 3-Jan-07 ghi 2 1-Jan-07 jkl 1 TIA, Earl |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match week, count rows, sum rows?
Count the rows, assuming the reference date is in E1
=COUNTIF(Sheet1!A:A,""&E1-7)-COUNTIF(Sheet1!A:A,""&E1) To sum them =SUMIF(Sheet1!A:A,""&E1-7,Sheet1!C:C)-SUMIF(Sheet1!A:A,""&E1,Sheet1!C:C) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "earls" wrote in message ... Hi, I've been westling with this and making no progress. I have columns Date, Subject, # figure. On another sheet I will have a reference date which is a week ending date for each week. I need to find all rows from the 1st sheet whose date falls in the same week as my week ending reference date, count the number of rows found, and sum the #figure for all rows found. Example: Reference date: = 5-Jan-07 A B C Date Subject # Figure 1-Jan-07 abc 3 4-Feb-07 def 1 3-Jan-07 ghi 2 1-Jan-07 jkl 1 TIA, Earl |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match week, count rows, sum rows?
Thanks Bob. This works as is... Earl
"Bob Phillips" wrote: Count the rows, assuming the reference date is in E1 =COUNTIF(Sheet1!A:A,""&E1-7)-COUNTIF(Sheet1!A:A,""&E1) To sum them =SUMIF(Sheet1!A:A,""&E1-7,Sheet1!C:C)-SUMIF(Sheet1!A:A,""&E1,Sheet1!C:C) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "earls" wrote in message ... Hi, I've been westling with this and making no progress. I have columns Date, Subject, # figure. On another sheet I will have a reference date which is a week ending date for each week. I need to find all rows from the 1st sheet whose date falls in the same week as my week ending reference date, count the number of rows found, and sum the #figure for all rows found. Example: Reference date: = 5-Jan-07 A B C Date Subject # Figure 1-Jan-07 abc 3 4-Feb-07 def 1 3-Jan-07 ghi 2 1-Jan-07 jkl 1 TIA, Earl |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count rows and insert number to count them. | Excel Discussion (Misc queries) | |||
| Count rows that match criteria in 2 different column cell ranges | New Users to Excel | |||
| Match Last Occurrence of Numeric Value and Count BACK to Previous | Excel Worksheet Functions | |||
| Match Each Numeric occurrence and Return Individual Rows of Data | Excel Worksheet Functions | |||
| Count rows based on multiple criteria | Excel Worksheet Functions | |||
Linear Mode
