# Thread: IF Formula - Date then amount (2000)

1. ## IF Formula - Date then amount (2000)

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.

2. ## Re: IF Formula - Date then amount (2000)

[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)

3. ## Re: IF Formula - Date then amount (2000)

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•