Counting number of cells between date range of a related column

Question

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

Wouldn't a simple [COUNTIFS](https://support.office.com/en-us/article/COUNTIFS-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842) do this? — Dirk Reichel, Mar 07 '16 at 22:25
How do you "choose" those two dates? By a filter? Do you specify their values in another cell? — ayhan, Mar 07 '16 at 22:31
I wouldn't say that... your approach will also work for unsorted ranges (to get the distance of cells, while countifs would count all cells that match regardless where they are inside the range)... it is more like "how to read the question" ;) — Dirk Reichel, Mar 07 '16 at 22:33
@ayhan the dates are in another call, say the date Column runs for a whole year and I want to count the orderIDs in each month — WillD, Mar 07 '16 at 22:45
Have tried the suggestions but doesn't seem to work for me (unless I'm doing something wrong). I want count if to work like sumif, if that makes sense so I can specify (doesn't matter how) two dates in column 1 and count the orderID cells within this range — WillD, Mar 07 '16 at 23:14

Scott Craner · Answer 1 · 2016-03-08T02:55:19.153

1

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 ;

edited Mar 08 '16 at 02:55

answered Mar 07 '16 at 22:09

Scott Craner

148,073
10
49
81

still: I like your approach. It has its own pros and cons :) – Dirk Reichel Mar 07 '16 at 23:13
1

I guess the second MATCH function's lookup array should also be A:A. – ayhan Mar 07 '16 at 23:40

score 0 · Accepted Answer · answered Mar 07 '16 at 22:43

0

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,

answered Mar 07 '16 at 22:43

nbayly

2,167
2
14
23

I tried this formula and =COUNT(INDEX(B:B,MATCH("06/03/2014",A:A)):INDEX(B:B,MATCH("09/03/2014",B:B)) ) assuming I have dates for years in A and orderIDs in B covering each day and returned the result 0 – WillD Mar 07 '16 at 23:18

score 0 · Answer 3 · answered Mar 07 '16 at 23:33

0

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?

answered Mar 07 '16 at 23:33

WillD

151
1
10

Yes and no... you could use a formula like `=AND(A1>=$D$1,A1<=$D$2)` having start and end in D1 and D2 starting in C1 and then copied down. Then just count the TRUE. which leads to the array formula `{=SUM(IFERROR((A:A>=D1)*(A:A<=D2),0))}` which leads again to the countifs `=COUNTIFS(A:A,">="&D1,A:A,"<="&D2)`. That would eliminate the need of a helper column completely. All you need is the amount of entries within the date range, so the order ID doesn't matter. Pls read the MS Excel help (the link from my first comment) COUNTIFS does exactly that and is suited perfectly for this task ;) – Dirk Reichel Mar 08 '16 at 06:18

Counting number of cells between date range of a related column

3 Answers3