Hi all

I am trying to find a formula that will sum the Value(s) were it matches the
specifed index number and where it falls between specific dates i.e. from
1/2/08 - 29/2/08
A B C
Index Invoice Date Value £
2 20/03/2008 £46,854.00
2 31/03/2008 £165,083.00
2 31/03/2008 £20,858.00
2 28/03/2008 £146,550.00
2 29/02/2008 £195,636.00

I have tried
=sum(if(a3:a7=2,if(b3:b7=&Date(2008,02,01),if(b3: b7<=&Date(2008,02,29),(c3:c7) - ARRAY - but does not work

Any ideas?

Try this formula....
=SUMPRODUCT(--(A2:A6=2),--(B2:B6=DATE(2008,3,1)),--
(B2:B6<=DATE(2008,3,31)),C2:C6)

Assumes index in col A rows 2-6
Date in col B rows 2-6
Value in col C rows 2-6


On May 30, 9:29 am, Peter wrote:
Hi all

I am trying to find a formula that will sum the Value(s) were it matches the
specifed index number and where it falls between specific dates i.e. from
1/2/08 - 29/2/08
A B C
Index Invoice Date Value £
2 20/03/2008 £46,854.00
2 31/03/2008 £165,083.00
2 31/03/2008 £20,858.00
2 28/03/2008 £146,550.00
2 29/02/2008 £195,636.00

I have tried
=sum(if(a3:a7=2,if(b3:b7=&Date(2008,02,01),if(b3: b7<=&Date(2008,02,29),(c3:c7) - ARRAY - but does not work

Any ideas?

Type:

=Sum(If(a3:a7=2,If(b3:b7=Date(2008,02,01),If(b3:b 7<=Date(2008,02,29),c3:c7)))

Then press: (as it is an array formula)
Cntr+Shft+Enter

Good Luck.

"Peter" wrote:

Hi all

I am trying to find a formula that will sum the Value(s) were it matches the
specifed index number and where it falls between specific dates i.e. from
1/2/08 - 29/2/08
A B C
Index Invoice Date Value £
2 20/03/2008 £46,854.00
2 31/03/2008 £165,083.00
2 31/03/2008 £20,858.00
2 28/03/2008 £146,550.00
2 29/02/2008 £195,636.00

I have tried
=sum(if(a3:a7=2,if(b3:b7=&Date(2008,02,01),if(b3: b7<=&Date(2008,02,29),(c3:c7) - ARRAY - but does not work

Any ideas?

Hi ND,

I would have thought this would work - but just gives me 0, no error.

Any other suggestions

"ND Pard" wrote:

Type:

=Sum(If(a3:a7=2,If(b3:b7=Date(2008,02,01),If(b3:b 7<=Date(2008,02,29),c3:c7)))

Then press: (as it is an array formula)
Cntr+Shft+Enter

Good Luck.

"Peter" wrote:

Hi all

I am trying to find a formula that will sum the Value(s) were it matches the
specifed index number and where it falls between specific dates i.e. from
1/2/08 - 29/2/08
A B C
Index Invoice Date Value £
2 20/03/2008 £46,854.00
2 31/03/2008 £165,083.00
2 31/03/2008 £20,858.00
2 28/03/2008 £146,550.00
2 29/02/2008 £195,636.00

I have tried
=sum(if(a3:a7=2,if(b3:b7=&Date(2008,02,01),if(b3: b7<=&Date(2008,02,29),(c3:c7) - ARRAY - but does not work

Any ideas?

"Peter" wrote:

Hi ND,

I would have thought this would work - but just gives me 0, no error.

Any other suggestions

"ND Pard" wrote:

Type:

=Sum(If(a3:a7=2,If(b3:b7=Date(2008,02,01),If(b3:b 7<=Date(2008,02,29),c3:c7)))

Then press: (as it is an array formula)
Cntr+Shft+Enter

Good Luck.

"Peter" wrote:

Hi all

I am trying to find a formula that will sum the Value(s) were it matches the
specifed index number and where it falls between specific dates i.e. from
1/2/08 - 29/2/08
A B C
Index Invoice Date Value £
2 20/03/2008 £46,854.00
2 31/03/2008 £165,083.00
2 31/03/2008 £20,858.00
2 28/03/2008 £146,550.00
2 29/02/2008 £195,636.00

I have tried
=sum(if(a3:a7=2,if(b3:b7=&Date(2008,02,01),if(b3: b7<=&Date(2008,02,29),(c3:c7) - ARRAY - but does not work

Any ideas?




Try

=SUM(IF(A3:A7=2,IF(B3:B7=DATE(2008,2,1),IF(B3:B7< =DATE(2008,2,29),C3:C7,0))))

enter as an array formula.

It works for me.

HTH

Hi ND and Pig5purt

I have tried both your formula's and have discovered that it only works if
the dates are manaully entered into the rows - at the moment the dates are
retrieved from another application.

Is there away around this without having to manually enter the dates?

"Pig5purt" wrote:



"Peter" wrote:

Hi ND,

I would have thought this would work - but just gives me 0, no error.

Any other suggestions

"ND Pard" wrote:

Type:

=Sum(If(a3:a7=2,If(b3:b7=Date(2008,02,01),If(b3:b 7<=Date(2008,02,29),c3:c7)))

Then press: (as it is an array formula)
Cntr+Shft+Enter

Good Luck.

"Peter" wrote:

Hi all

I am trying to find a formula that will sum the Value(s) were it matches the
specifed index number and where it falls between specific dates i.e. from
1/2/08 - 29/2/08
A B C
Index Invoice Date Value £
2 20/03/2008 £46,854.00
2 31/03/2008 £165,083.00
2 31/03/2008 £20,858.00
2 28/03/2008 £146,550.00
2 29/02/2008 £195,636.00

I have tried
=sum(if(a3:a7=2,if(b3:b7=&Date(2008,02,01),if(b3: b7<=&Date(2008,02,29),(c3:c7) - ARRAY - but does not work

Any ideas?




Try

=SUM(IF(A3:A7=2,IF(B3:B7=DATE(2008,2,1),IF(B3:B7< =DATE(2008,2,29),C3:C7,0))))

enter as an array formula.

It works for me.

HTH

On Fri, 30 May 2008 06:29:01 -0700, Peter
wrote:

Hi all

I am trying to find a formula that will sum the Value(s) were it matches the
specifed index number and where it falls between specific dates i.e. from
1/2/08 - 29/2/08
A B C
Index Invoice Date Value £
2 20/03/2008 £46,854.00
2 31/03/2008 £165,083.00
2 31/03/2008 £20,858.00
2 28/03/2008 £146,550.00
2 29/02/2008 £195,636.00

I have tried
=sum(if(a3:a7=2,if(b3:b7=&Date(2008,02,01),if(b3 :b7<=&Date(2008,02,29),(c3:c7) - ARRAY - but does not work

Any ideas?

In general:

=sumif(Invoice_Date,"="&date(2008,2,1),Value)-
sumif(Invoice_Date,""&date(2008,2,29),Value)

The date(2008,2,1) function may be replaced by a cell reference where the cell
contains the appropriate date.

Note that the equality in the criteria is within quotes, and concatenated with
the date.
--ron