Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF with dates | Excel Worksheet Functions | |||
Sumif & Dates | Excel Worksheet Functions | |||
SUMIF and Dates | Excel Worksheet Functions | |||
SUMIF USING DATES | Excel Discussion (Misc queries) | |||
SUMIF between dates | Excel Worksheet Functions |