Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sum between a range of dates
I want to get the sum between a range of dates.
H8:S8 contain the dates H11:S11 contains the values V5 is the start date V6 is the end date I have tried the following as an array formula but if only works if V5 and V6 are at least that of range H8:S8 =SUM(IF(AND(H8:S8=V5,H8:S8<=V6),H11:S11)) In other words I want it to sum the cell the do meet the criteria. Any ideas? |
#2
|
|||
|
|||
=SUMIF($H$8:$S$8,"="&V5,$H$11:$S$11)-SUMIF($H$8:$S$8,""&V6,$H$11:$S$11)
which just needs enter. Just a comment: The array formula you tried invokes AND() which is not appropriate for it can just return a single value, not an array. For the latter you need to use the multiplication operator. Bruce wrote: I want to get the sum between a range of dates. H8:S8 contain the dates H11:S11 contains the values V5 is the start date V6 is the end date I have tried the following as an array formula but if only works if V5 and V6 are at least that of range H8:S8 =SUM(IF(AND(H8:S8=V5,H8:S8<=V6),H11:S11)) In other words I want it to sum the cell the do meet the criteria. Any ideas? |
#3
|
|||
|
|||
Try:
=SUMPRODUCT((H8:S8=V5)*(H8:S8<=V6),H11:S11) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Bruce" wrote in message ... I want to get the sum between a range of dates. H8:S8 contain the dates H11:S11 contains the values V5 is the start date V6 is the end date I have tried the following as an array formula but if only works if V5 and V6 are at least that of range H8:S8 =SUM(IF(AND(H8:S8=V5,H8:S8<=V6),H11:S11)) In other words I want it to sum the cell the do meet the criteria. Any ideas? |
#4
|
|||
|
|||
Close. Try:
=SUM(IF((H8:S8=V5)*(H8:S8<=V6),H11:S11)) Array-entered. HTH Jason Atlanta, GA -----Original Message----- I want to get the sum between a range of dates. H8:S8 contain the dates H11:S11 contains the values V5 is the start date V6 is the end date I have tried the following as an array formula but if only works if V5 and V6 are at least that of range H8:S8 =SUM(IF(AND(H8:S8=V5,H8:S8<=V6),H11:S11)) In other words I want it to sum the cell the do meet the criteria. Any ideas? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
Range of dates determine days | Excel Worksheet Functions | |||
named range refers to: in a chart | Excel Discussion (Misc queries) | |||
can I use a range of dates as a criteria when using sumif? | Excel Worksheet Functions | |||
I have a list of dates that I need to count based on a date range | Excel Worksheet Functions |