This post will guide you how to count cells between two dates with multiple criteria in excel. How do I count records between two dates and multiple criteria with formula in excel. You can easily count cells between two dates using COUNTIFS function. and if you want to count records between two dates with two or more criteria, how to achieve it.
Count Cells between Two Dates with Multiple Criteria
Assuming that you have a list of data that contain two columns, and one column contain product name and another column contain date values, and you want to count the number of product “excel” which is between two dates (from 2018/8/9-2018/10/19) in column B. You can create a new excel formula based on the SUMPRODUCT function to achieve the result.
=SUMPRODUCT(--($B$2:$B$7>=$D$2), --($B$2:$B$7<=$D$3), --($A$2:$A$7="excel"))
Type this formula into a blank cell and then press Enter key in your keyboard.
You will see that the count number will be shown.
If you want to count the number of the product “excel” and “word” which between two dates in column B, and you can refer to the above formula to write down another complex formula based on the SUMPRODUCT function and the COUNTIF function.
=SUMPRODUCT(--($B$2:$B$7>=$D$2), --($B$2:$B$7<=$D$3), --(COUNTIF($D$1:$E$1, $A$2:$A$7)))
Type this formula into a blank cell and press Enter key. you will get the result.
- Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
- Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…