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

Nebulous

New Member
Joined
Oct 7, 2015
Messages
37
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.


ABCDEFG
1PDSDCostSD PDSLDollar Total
210/14/169/30/16$5,869.46
310/14/169/30/16$11,096.99
410/7/1610/3/16$6,043.00
510/7/1610/3/16$3,416.44
611/21/1611/14/16$6,043.00
711/21/168/14/16$8,185.76
811/18/1611/14/16$8,008.80
911/18/1611/14/16$11,096.99
1012/2/1611/14/16$5,557.04
1112/16/1611/14/16$3,416.44
1211/18/1611/14/16$5,869.46
1312/9/16$7,936.66
1412/9/16$7,936.66
1512/9/16$7,936.66
1612/9/16$72,122.30
1712/9/16$69,936.89
1812/9/16$69,936.89
1912/9/16$13,237.83
2011/30/16$84,215.12
211/23/17$84,309.02
221/27/17$84,309.02
232/8/17$92,369.53
242/10/17$90,243.89
252/10/17$90,227.02

<tbody>
</tbody>
Thanks!

~N
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
So what outputs are you looking to get? The setup is a bit confusing.
 
Upvote 0
Thanks - I agree it's confusing. I was struggling to find a way to represent my Excel table after I found out that my screenshot wasn't allowed (it had notes and arrows).

E2: I want this to report the number of cells in Column B whose dates are the same as or earlier than the corresponding dates in Column A
[Count B2 if it is the same date or earlier than the date in A2, etc.)

F2: I want this to sum up the dollar values from Column C for the items counted in E2
[If B2 and B7 are part of the result of E2, then F2 would include their dollar values in its sum]

The result from this sample data should be E2=11 and F2=$74,603.38
[Because the first 11 items in B are all earlier than the dates in A]

I should have provided better sample data since all of those are earlier. Hopefully this makes sense, though. Again, there will sometimes be blank cells, and they may be interspersed through Columns A or B

~N
 
Last edited:
Upvote 0
Eric - I had trouble creating this post at first. I thought I could include a screenshot. When I learned that I couldn't (even though the editor displayed it when I pasted it), I created this new post thinking I would then delete the old one. But I see no way to delete the old one.

I hate having multiples out there. Sorry.
 
Upvote 0
Sorry, you can't delete a post, even if you created it. :( Also, as you've seen, you can't paste an image. You can post a link to an image that's hosted on another web site, but that's not ideal. You did manage to create a nice table in the first post of this thread. You can also use something like the HTML Maker (see the link in my signature) to create a table you can paste into a post.

Have you checked the other thread? I posted some possible formulas that might work there.
 
Upvote 0
Cyrus and Eric - Thanks for the attention. [I discovered and used HTML Maker to create the table at the top of this thread].

Eric - I haven't checked the other post. I will after this.

Cyrus: In this case, the correct answers should be E2=11 and F2=$74,603.38
 
Upvote 0
Nebulous,

Why wasn't Eric's answer (below) correct?

How about:

E2: =SUMPRODUCT(--(B2:B25<>""),--(B2:B25<=A2:A25))

F2: =SUMPRODUCT(--(B2:B25<>""),--(B2:B25<=A2:A25),C2:C25)

Because you said it worked, but then wrote:

Actually, upon further though, not quite perfect. I'm going to abandon this post and reply to you in the duplicate (and include this solution).

Continued at Counting dates in one column that are earlier than another column summing their dollar values
~N
 
Last edited:
Upvote 0
I can't always count on there being 25 rows, so I simply tweaked it to:
E2: =SUMPRODUCT(--(B:B<>""),--(B:B<=A:A))
F2: =SUMPRODUCT(--(B:B<>""),--(B:B<=A:A),C:C)

Thanks for all the help!

jb
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top