SUMIF using dates in cells
 

I need to sum a column of numbers in cells B1 - B5 from the range in Column A
from a date greater than the one in cell C1. How do I do this?

Example

A1 = 01/01/2007 B1 = 13 C1 = 20/02/2007
A2 = 01/02/2007 B2 = 4
A3 = 01/03/2007 B3 = 9
A4 = 01/04/2007 B4 = 31
A5 = 01/05/2007 B5 = 6

In this example, the sum total would be 46. How would I write that as a
formula? Would the sum total update if I changed the C1 date?

SUMIF using dates in cells
 

Try this:-

=SUMPRODUCT((A1:A5C1)*(B1:B5))

Mike

"Annie99" wrote:

I need to sum a column of numbers in cells B1 - B5 from the range in Column A
from a date greater than the one in cell C1. How do I do this?

Example

A1 = 01/01/2007 B1 = 13 C1 = 20/02/2007
A2 = 01/02/2007 B2 = 4
A3 = 01/03/2007 B3 = 9
A4 = 01/04/2007 B4 = 31
A5 = 01/05/2007 B5 = 6

In this example, the sum total would be 46. How would I write that as a
formula? Would the sum total update if I changed the C1 date?

SUMIF using dates in cells
 

or if you really want sumif try:-

=SUMIF(A1:A5,""&C1,B1:B5)

Mike

"Annie99" wrote:

I need to sum a column of numbers in cells B1 - B5 from the range in Column A
from a date greater than the one in cell C1. How do I do this?

Example

A1 = 01/01/2007 B1 = 13 C1 = 20/02/2007
A2 = 01/02/2007 B2 = 4
A3 = 01/03/2007 B3 = 9
A4 = 01/04/2007 B4 = 31
A5 = 01/05/2007 B5 = 6

In this example, the sum total would be 46. How would I write that as a
formula? Would the sum total update if I changed the C1 date?

SUMIF using dates in cells
 

That worked....

If I wanted to change the example to include between 2 dates....
For example
C1 = 20/02/2007
C2 = 16/04/2007

How could I do that in the same formula?

"Mike H" wrote:

Try this:-

=SUMPRODUCT((A1:A5C1)*(B1:B5))

Mike

"Annie99" wrote:

I need to sum a column of numbers in cells B1 - B5 from the range in Column A
from a date greater than the one in cell C1. How do I do this?

Example

A1 = 01/01/2007 B1 = 13 C1 = 20/02/2007
A2 = 01/02/2007 B2 = 4
A3 = 01/03/2007 B3 = 9
A4 = 01/04/2007 B4 = 31
A5 = 01/05/2007 B5 = 6

In this example, the sum total would be 46. How would I write that as a
formula? Would the sum total update if I changed the C1 date?

SUMIF using dates in cells
 

Annie,

You do it using subtraction:-

=SUMPRODUCT((A1:A5<C2)*(B1:B5))-SUMPRODUCT((A1:A5<C1)*(B1:B5))

Mike

"Annie99" wrote:

That worked....

If I wanted to change the example to include between 2 dates....
For example
C1 = 20/02/2007
C2 = 16/04/2007

How could I do that in the same formula?

"Mike H" wrote:

Try this:-

=SUMPRODUCT((A1:A5C1)*(B1:B5))

Mike

"Annie99" wrote:

I need to sum a column of numbers in cells B1 - B5 from the range in Column A
from a date greater than the one in cell C1. How do I do this?

Example

A1 = 01/01/2007 B1 = 13 C1 = 20/02/2007
A2 = 01/02/2007 B2 = 4
A3 = 01/03/2007 B3 = 9
A4 = 01/04/2007 B4 = 31
A5 = 01/05/2007 B5 = 6

In this example, the sum total would be 46. How would I write that as a
formula? Would the sum total update if I changed the C1 date?

SUMIF using dates in cells
 

=SUMPRODUCT((A1:A5C1)*(A1:A5<C2)*(B1:B5))

Decide whether you want or =, and similarly < or <=.
--
David Biddulph

"Annie99" wrote in message
...
That worked....

If I wanted to change the example to include between 2 dates....
For example
C1 = 20/02/2007
C2 = 16/04/2007

How could I do that in the same formula?

"Mike H" wrote:

Try this:-

=SUMPRODUCT((A1:A5C1)*(B1:B5))

Mike

"Annie99" wrote:

I need to sum a column of numbers in cells B1 - B5 from the range in
Column A
from a date greater than the one in cell C1. How do I do this?

Example

A1 = 01/01/2007 B1 = 13 C1 = 20/02/2007
A2 = 01/02/2007 B2 = 4
A3 = 01/03/2007 B3 = 9
A4 = 01/04/2007 B4 = 31
A5 = 01/05/2007 B5 = 6

In this example, the sum total would be 46. How would I write that as
a
formula? Would the sum total update if I changed the C1 date?