Summing Data that Falls within 2 Criteria
Posted by Ryan R. Andeson on March 16, 2000 8:34 AM
I would appreciate anyone's assistance with this....
I have monthly dollar data and I want to obtain a sum of this data between 2 months that I specify. However, I cannot seem to find a formula that will allow me to do this.
More info: A4 to AA4 = months(i.e. 1/31/99..etc)
Column A5:A25 Regions
I want to add the dollar amounts if they fall between the dates I specify, such as 1/31/99 and 1/31/00.
Thank you for any assistance,
RRA
Posted by Aaron :) on March 16, 2000 9:40 AM
=SUMIF(MonthRange,">="&Date1,ValRange)-SUMIF(MonthRange,">"&Date2,ValRange)
Posted by Ryan Anderson on March 16, 2000 11:16 AM
Aaron:
here is the formula I have devised per your advice, but it does not seem to work:
@SUMIF(Months,>=B2,Data!B6:N50)-SUMIF(Months,>C2,Data!B6:N50)
Does the data range have to include the month headings across the row?
Posted by Aaron :) on March 16, 2000 12:53 PM
The value range should be a single row and you can't leave off the quotes and the ampersand.
(assumes "Months" is a single row of dates, B2 is start date, C2 is end date)
=SUMIF(Months,">="&B2,Data!B50:N50)-SUMIF(Months,">"&C2,Data!B50:N50)
Posted by Ryan R. Andeson on March 17, 2000 6:44 AM
Follow Up:
Aaron:
Thank you very much for all of your assistance. One last question for you...
This formula seems like it will work for me. However, the 2nd part of the formula subtracts everything less than C2(end date). So, I do not get a total of the data between the 2 dates:Here is what I get
Sum of data greater than or equal to 1/31/99 - sum of data less than 11/30/99. What I am after is the total data that falls between 1/31/99 and 11/30/99, or whatever dates I change the input cells to.
Thanks and have a great St.Patricks Day
RRA
Posted by Aaron :) on March 17, 2000 9:14 AM
Ryan,
Look at the formula.
=SUMIF(Months,">="&B2,Data!B50:N50)-SUMIF(Months,">"&C2,Data!B50:N50)
How do you suppose it would need to change to sum the items greater than date1 and less than date2?
.
.
.
=SUMIF(Months,">"&B2,Data!B50:N50)-SUMIF(Months,">="&C2,Data!B50:N50)
