Prev: Use of "AND"
Next: Custom Spreadsheet Properties
From: Dwells on 23 Feb 2010 13:48
i'm using this to add values that are between 2 dates
=SUMPRODUCT(--(G8:G26>=DATEVALUE("1/1/2010")),--(G8:G26<=DATEVALUE("1/30/2010")),F8:F26)

but i need the dates 1/1/2010 and 1/30/2010 to be user input. so that those
values are reading from another cell. is this possible
From: Mike H on 23 Feb 2010 13:56
Hi,

Simply use cell references for the dates

=SUMPRODUCT(--(G8:G26>=A1),--(G8:G26<=A2),F8:F26)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Dwells" wrote:

> i'm using this to add values that are between 2 dates
> =SUMPRODUCT(--(G8:G26>=DATEVALUE("1/1/2010")),--(G8:G26<=DATEVALUE("1/30/2010")),F8:F26)
>
> but i need the dates 1/1/2010 and 1/30/2010 to be user input. so that those
> values are reading from another cell. is this possible
From: Pete_UK on 23 Feb 2010 13:57
If you put the start date in A1 and the end date in B1 (both in Excel
date format), then you can have:

=SUMPRODUCT(--(G8:G26>=A1),--(G8:G26<=B1),F8:F26)

Hope this helps.

Pete

On Feb 23, 6:48 pm, Dwells <Dwe...(a)discussions.microsoft.com> wrote:
> i'm using this to add values that are between 2 dates
> =SUMPRODUCT(--(G8:G26>=DATEVALUE("1/1/2010")),--(G8:G26<=DATEVALUE("1/30/20­10")),F8:F26)
>
> but i need the dates 1/1/2010 and 1/30/2010 to be user input. so that those
> values are reading from another cell. is this possible

From: T. Valko on 23 Feb 2010 17:10
Try one of these...

A8 = lower date boundary = 1/1/2010
B8 = upper date boundary = 1/30/2010

=SUMIF(G8:G26,">="&A8,F8:F26)-SUMIF(G8:G26,">"&B8,F8:F26)

If you're using Excel 2007:

=SUMIFS(F8:F26,G8:G26,">="&A8,G8:G26,"<="&B8)

--
Biff
Microsoft Excel MVP


"Dwells" <Dwells(a)discussions.microsoft.com> wrote in message
news:8C41F58D-3180-4205-8A7E-6694FC5F711B(a)microsoft.com...
> i'm using this to add values that are between 2 dates
> =SUMPRODUCT(--(G8:G26>=DATEVALUE("1/1/2010")),--(G8:G26<=DATEVALUE("1/30/2010")),F8:F26)
>
> but i need the dates 1/1/2010 and 1/30/2010 to be user input. so that
> those
> values are reading from another cell. is this possible


 | 
Pages: 1
Prev: Use of "AND"
Next: Custom Spreadsheet Properties