CODEWITHSUNDEEP
excelcountcountif
WillD
WillD

Reputation: 151

Counting number of cells between date range of a related column

Excel question

Two columns and want to count the cells in column 2 if they are between two chosen dates in column 1

column 1                column 2
Date of order        order id 
11/11/2008            4361
12/11/2008            4363
13/11/2008            4368
14/11/2008            4333
15/11/2008            4222
16/11/2008            4893
17/11/2008            4872
13/11/2008            4368
14/11/2008            4333
15/11/2008            4222
16/11/2008            4893
17/11/2008            4872

Upvotes: 0

Views: 1025

Answers (3)

Scott Craner
Scott Craner

Reputation: 152505

Try this:

=COUNT(INDEX(B:B,MATCH("11/11/2008",A:A)):INDEX(B:B,MATCH("15/11/2008",A:A))

You can change the dates to cell references that contain the first and last date. Column A must be sorted.

Your local setting may require the tha , delimiter be ; instead. If so replace all the , with ;

Upvotes: 1

WillD
WillD

Reputation: 151

Could this be achieved easier with a helper column? Assuming both columns are fully populated, is there a way to place a "1" in column 3 if an orderID lays within 2 date ranges?

Upvotes: 0

nbayly
nbayly

Reputation: 2167

Please consider using the following formula:

=COUNTIFS($A:$A,">="&StartDate,$A:$A,"<="&EndDate)

Note that this assumes that column 2 has all rows populated. Regards,

Upvotes: 0

Related Questions