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

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

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