match week, count rows, sum rows? in Excel Worksheet Functions  
 |  Edit my Profile  |  Help
 
     
   
 
 
 
earls 1/4/2007 2:35 PM PST
  Question
  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




 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Bob Phillips 1/4/2007 3:04 PM PST
  Answer
  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" <earls@discussions.microsoft.com> wrote in message
news:CDB3C24D-7D6C-4856-A9F6-4E36F4BA1349@microsoft.com...
> 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
>
>
>
>
>


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
earls 1/4/2007 6:33 PM PST
   
  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" <earls@discussions.microsoft.com> wrote in message
> news:CDB3C24D-7D6C-4856-A9F6-4E36F4BA1349@microsoft.com...
> > 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
> >
> >
> >
> >
> >
>
>
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
  Return to Microsoft Communities  Notify me of replies