# Help needed: IF functions with dates as criteria

#### Bob Dobelina

##### New Member
Wanting to create a SUMIF which counts values occuring within a certain date range. It's easy enough to SUMIF when the criterion is say, ">0" or "Paid" or similar. But how do you do it with dates? Say I want to sum all invoices paid within November... as in the function below on the bottom right:

Schedule

Client Payment Date Total Fee
Client 1 25/11/2005 \$8,000
Client 2 7/12/2005 \$5,600
Client 3 6/01/2006 \$3,500
Total \$17,100

Fee forecast

Month Nov-05 Dec-05 Jan-06
\$8,000 \$5,600 SUMIF(refer vertical range of dates, as long as the date falls within Jan 2006, refer vertical range of fees)

(I know the example is crowded, messy and poorly formatted; one day I'll post another question to see if I can't learn to post such examples properly!
Can't install Colo's HTML Maker either as not an administrator on my workstation. I hope you get the general idea anyway!).

Generally speaking, if there are general guidelines for IF functions using dates as criteria, what are they? If anyone has any ideas, please do share!

Kind regards,

Bob

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Hi Bob:

Here is one way, using the SUMPRODUCT function ...
Book1
ABCDEFGH
1ClientPaymentDate01-Nov-0501-Dec-0501-Jan-0601-Feb-06
2Client125-Nov-05\$8,00017040560015800
3Client207-Dec-05\$5,60017040560015800
4Client301-Jan-06\$3,500
5Client128-Nov-05\$1,200
6Client213-Jan-06\$12,300
7Client330-Nov-05\$7,840
Sheet1

formula in cell E2 is ...
Code:
=SUMPRODUCT((\$B\$2:\$B\$7>=E\$1)*(\$B\$2:\$B\$7<F\$1),\$C\$2:\$C\$7)

this is then copied to the right.

or alternately formula in cell E3 is ...
Code:
=SUMPRODUCT((MONTH(\$B\$2:\$B\$7)=MONTH(E\$1))*(\$C\$2:\$C\$7))

Please have a look at the formulas, and post back if you need to discuss this further.

Assuming your date is in cell B1:B3 and your amount is c1:c3, to sum those paid on or before november
=SUMIF(B1:B3,"<"&DATEVALUE("11/30/2005"),C1:C3)

Replies
2
Views
213
Replies
3
Views
341
Replies
3
Views
815
Replies
12
Views
504
Replies
5
Views
520

1,196,048
Messages
6,013,095
Members
441,747
Latest member
darkman77

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back