Counting dates in one column that are earlier than another column summing their dollar values

[Nebulous]

In the worksheet below, I need the count and dollar value totals of lines in Column B that are the same as or earlier than Column A to be reported in E2 and F2.

I tried with various attempts at COUNTIF but failed. I'm not an Excel expert - Intermediate at best.

NOTE: There will be blank cells as there are in this table, though they may not always be sorted to the bottom of the list as they are here.

	A	B	C	D	E	F	G
1	PD	SD	Cost		SD PDSL	Dollar Total
2	10/14/16	9/30/16	$5,869.46
3	10/14/16	9/30/16	$11,096.99
4	10/7/16	10/3/16	$6,043.00
5	10/7/16	10/3/16	$3,416.44
6	11/21/16	11/14/16	$6,043.00
7	11/21/16	8/14/16	$8,185.76
8	11/18/16	11/14/16	$8,008.80
9	11/18/16	11/14/16	$11,096.99
10	12/2/16	11/14/16	$5,557.04
11	12/16/16	11/14/16	$3,416.44
12	11/18/16	11/14/16	$5,869.46
13	12/9/16		$7,936.66
14	12/9/16		$7,936.66
15	12/9/16		$7,936.66
16	12/9/16		$72,122.30
17	12/9/16		$69,936.89
18	12/9/16		$69,936.89
19	12/9/16		$13,237.83
20	11/30/16		$84,215.12
21	1/23/17		$84,309.02
22	1/27/17		$84,309.02
23	2/8/17		$92,369.53
24	2/10/17		$90,243.89
25	2/10/17		$90,227.02

<tbody>
</tbody>

Thanks!

~N

 

[Eric W]

For performance reasons, you may wish to use an upper limit, say B2:B1000. Whatever makes sense for your data. If you use an entire column reference, the formula has to look at over a million rows each time it calculates, which can take some time. COUNTIFS and SUMIFS are more efficient with whole column references, but they can't do everything that SUMPRODUCT can either.

Glad we could help!

 

[Nebulous]

Good point, Eric. Though I could potentially have 1000s of rows, I wouldn't have millions. I'll cap it at 10,000 which may provide at least some performance improvement.

 

[GnailD]

Maybe I am missing the point but countif and sumif seem to work for me. The formula in E2 is "=COUNTIF($B$2:$B$12,"<="&A2)" and the formula in F2 is "=SUMIF($B$2:$B$25,"<="&A2,$C$2:$C$25)"