Help needed: IF functions with dates as criteria

[Bob Dobelina]

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

 

[Yogi Anand]

Hi Bob:

Here is one way, using the SUMPRODUCT function ...

Book1
	A	B	C	D	E	F	G	H
1	Client	Payment	Date		01-Nov-05	01-Dec-05	01-Jan-06	01-Feb-06
2	Client1	25-Nov-05	$8,000		17040	5600	15800
3	Client2	07-Dec-05	$5,600		17040	5600	15800
4	Client3	01-Jan-06	$3,500
5	Client1	28-Nov-05	$1,200
6	Client2	13-Jan-06	$12,300
7	Client3	30-Nov-05	$7,840
Sheet1

formula in cell E2 is ...

=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 ...

=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.

 

[Chitosunday]

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)