I am trying to sum values in a one column if the dates in a row fall within
a date range. There may be multiple occurrences of dates that occur in that
given range and therefore the value in the sum column needs to be added as
many times as that occurance. I have tried to to use a sumifs statement but
it seems only to be adding the value if it appears once in that row. I have
tried to add a table below that illustrates the question but I am not sure
the formatting will show. Any help will be greatly appreciated. I have
excel 2007.

Col A Col B Col C Col D
row 1 Units Price Sold
row 2 4 $20 Jan-10 Feb-12
row 3 2 $5 Jan-09 Jan-10
Row 4 3 $7 Jan-10 Feb-10

The results I need are as follows ( Just sums all the occurances of a unit
being sold for the date range)

2009 2010 2011 2012
Sales $5 $39 $- $20

I used this formula for the year 2010 and made it an array

=SUMIFS(b2:b4,c2:d4,"="&Date(2010,1,1),c2:d4,"<"& Date(2011,1,1))

--
Celia

Try
=SUMIFS(B2:B4,C2:C4,"="&DATE(2010,1,1),D2:D4,"<"& DATE(2011,1,1))

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"Celia" wrote:

I am trying to sum values in a one column if the dates in a row fall within
a date range. There may be multiple occurrences of dates that occur in that
given range and therefore the value in the sum column needs to be added as
many times as that occurance. I have tried to to use a sumifs statement but
it seems only to be adding the value if it appears once in that row. I have
tried to add a table below that illustrates the question but I am not sure
the formatting will show. Any help will be greatly appreciated. I have
excel 2007.

Col A Col B Col C Col D
row 1 Units Price Sold
row 2 4 $20 Jan-10 Feb-12
row 3 2 $5 Jan-09 Jan-10
Row 4 3 $7 Jan-10 Feb-10

The results I need are as follows ( Just sums all the occurances of a unit
being sold for the date range)

2009 2010 2011 2012
Sales $5 $39 $- $20

I used this formula for the year 2010 and made it an array

=SUMIFS(b2:b4,c2:d4,"="&Date(2010,1,1),c2:d4,"<"& Date(2011,1,1))

--
Celia

I don't think that will work because if there is a date of 2012 in column C
that should pick it up and is there is a 2009 date in column D that will also
get picked up
--
Celia


"Sheeloo" wrote:

Try
=SUMIFS(B2:B4,C2:C4,"="&DATE(2010,1,1),D2:D4,"<"& DATE(2011,1,1))

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"Celia" wrote:

I am trying to sum values in a one column if the dates in a row fall within
a date range. There may be multiple occurrences of dates that occur in that
given range and therefore the value in the sum column needs to be added as
many times as that occurance. I have tried to to use a sumifs statement but
it seems only to be adding the value if it appears once in that row. I have
tried to add a table below that illustrates the question but I am not sure
the formatting will show. Any help will be greatly appreciated. I have
excel 2007.

Col A Col B Col C Col D
row 1 Units Price Sold
row 2 4 $20 Jan-10 Feb-12
row 3 2 $5 Jan-09 Jan-10
Row 4 3 $7 Jan-10 Feb-10

The results I need are as follows ( Just sums all the occurances of a unit
being sold for the date range)

2009 2010 2011 2012
Sales $5 $39 $- $20

I used this formula for the year 2010 and made it an array

=SUMIFS(b2:b4,c2:d4,"="&Date(2010,1,1),c2:d4,"<"& Date(2011,1,1))

--
Celia

Celia,
I think what you need is this
=SUMIFS(B2:B4,C2:C4,"="&DATE(2010,1,1),C2:C4,"<"& DATE(2011,1,1)) +
=SUMIFS(B2:B4,D2:D4,"="&DATE(2010,1,1),D2:D4,"<"& DATE(2011,1,1))

Of course this will add up a value twice if it meets the conditon in both
Col C and Col D, but I guess you have set it to avoid this situation.
--------------------------------------------------------------
=SUMIFS(B2:B4,C2:C4,"="&DATE(2010,1,1),D2:D4,"<"& DATE(2011,1,1))
This will the values in B2:B4 only if date in Col C in the same row is =
1/1/2010 AND date in Col D in the same row is <1/1/2011
If that is not what you want then my proposed solution will not work...
This is what you had asked for -
"I am trying to sum values in a one column if the dates in a row fall within
a date range. "
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"Celia" wrote:

I don't think that will work because if there is a date of 2012 in column C
that should pick it up and is there is a 2009 date in column D that will also
get picked up
--
Celia


"Sheeloo" wrote:

Try
=SUMIFS(B2:B4,C2:C4,"="&DATE(2010,1,1),D2:D4,"<"& DATE(2011,1,1))

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"Celia" wrote:

I am trying to sum values in a one column if the dates in a row fall within
a date range. There may be multiple occurrences of dates that occur in that
given range and therefore the value in the sum column needs to be added as
many times as that occurance. I have tried to to use a sumifs statement but
it seems only to be adding the value if it appears once in that row. I have
tried to add a table below that illustrates the question but I am not sure
the formatting will show. Any help will be greatly appreciated. I have
excel 2007.

Col A Col B Col C Col D
row 1 Units Price Sold
row 2 4 $20 Jan-10 Feb-12
row 3 2 $5 Jan-09 Jan-10
Row 4 3 $7 Jan-10 Feb-10

The results I need are as follows ( Just sums all the occurances of a unit
being sold for the date range)

2009 2010 2011 2012
Sales $5 $39 $- $20

I used this formula for the year 2010 and made it an array

=SUMIFS(b2:b4,c2:d4,"="&Date(2010,1,1),c2:d4,"<"& Date(2011,1,1))

--
Celia