How to count values that exist in other column within a range in Excel?-CodePudding

If I have table 1 with OrderIDs and CallIDs and table 2 with CallIDs (that are referenced in table 1) and callDates, how can I find the number of orders within a given date range?

See image of tables

I believe what I need to do is get the calls that are within a date range in table 2, then check if the CallIDs exist in table 1, then get the count of the result. I just do not know how to put it all together in a formula. For instance in the picture, the number of orders in February should be 3.

CodePudding user response：

=SUM(COUNTIFS(E2:E15,">="&DATE(2022,2,1),E2:E15,"<"&DATE(2022,3,1),D2:D15,B2:B8))

COUNTIFS creates an array of 0's and 1's (respectively FALSE and TRUE) for the date range in column E containing values greater than or equal to February 1st and smaller than March 1st and where the call number from column D is found in column B. SUM adds up the 1's and 0's resulting in your count of matches in that range.