Excel Count number of cells with value between date range - Excel

S

Sumo

Joined
Jul 18, 2005
Messages
1
Reaction score
0
I need to work out a formula that will allow me to count the number of products despatched between two dates. The dates are in cells A1 and B1, and the other cells with dates of despatch in are in cell C1 to C100. I want to have another cell which basically counts the number of items despatchedbetween the dates in A1 and B1. Can anyone help?
 
Last edited:
P

PapaBear56

Joined
Jul 19, 2005
Messages
3
Reaction score
0
I'm sure there may be a better way to do it but one, quick and dirty way would be to use column D with a formula that tests each "C" cell to see if it falls between A1 and B1. If it does, have the formula print an "X" and then simply count the "X's".

D Column Formula: =if(and($c1>=$a$1,$c1<=$b$1),"X","") -- Put formula in cell D1 and drag through D100
Cell D101: =countif($c$1:C$100,"X")

A simpler way could also be to put a formula in cell C101: =subtotal(9,$c$1:$C$100) -- After you place the formula, you then apply a filterto cells C1:C100 testing for greater than/or equal to A1 and testing for less than/or equal to B1. Apply the filter with the C1 through C100 highlighted and view the "COUNT" in the status bar.

I hope this helps!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Sumproduct 0
Excel Macro to automatically go to the right of the last colored cell 0
Cells with a value between two dates 1
Excel Add ^ to a row of cells containing letters 3
Excel Need Countifs Formula Help 0
Excel Repeating a RAND() function under certain conditions 0
Excel Date Setting 1
Excel Use COUNTIF across a range of cells with formulas 0

Top