IF Formula – Date then amount (2000)

Topic

New Reply

I am attempting to create an IF (or SUMIF) formula that looks through its range
1st: for a specified date range, then
2nd: adds the dollar amounts for any fields that match the criteria
I can’t seem to figure out how to input the criteria for a date range of one month

For example, I want the formula to search though a list of dates and pick out all of the cells that are, say, from Jan 1 – Jan 31, and then look at the amount that corresponds to that date and add up all of those values into one. This is the end result of the formula – a dollar value.

Confused? Me too. Any help is greatly appreciated.

Reply | Quote

Replies

[Edited, munged the syntax]

=SUMIF(daterange,”>=1/1/2002″,amountrange)-SUMIF(daterange,”>1/31/2002″,amountrange)

If you want to reference the dates rather than hard code:

=SUMIF(daterange,”>=”&TEXT(startdatecell,”mm/dd/yyyy”),amountrange)-SUMIF(daterange,”>”&TEXT(enddatecell,”mm/dd/yyyy”),amountrange)

Reply | Quote

The question involves totaling amounts involving a criterion month.

Lets say that A2:A100 houses full date entries and B2:B100 dollar amounts.

In order to use the SUMIF effectively, which is an appropriate function for the task, you need the following:

In C2 enter: =MONTH(A2) [or =TEXT(A2,”MMM”) ]

Copy down this formula till C100.

In D2 enter: a month number of interest if column C created with =MONTH(..) or a 3-letter month name if column C is created with =TEXT(…,”MMM”),

In E2 enter: =SUMIF($C$2:$C$100,D2,$B$2:$B$100)

Another method, where you don’t need to create column C, would be with SUMPRODUCT.

If D2 is a month number (the criterion month),

in E2 enter: =SUMPRODUCT((MONTH($A$2:$A$200)=D2)*($B$2:$B$100))

Or, if D2 is a 3-letter month name (the criterion month),

in E2 enter: =SUMPRODUCT((TEXT($A$2:$A$200,”MMM”)=D2)*($B$2:$B$100))

The first method (with SUMIF) should be preferred above the second if the actual data range consists of more than 1000 rows.

Reply | Quote