Excel COUNTIF Function for Two Conditions
This short Excel tip shows how to use Excel's COUNTIF function to count the number of data points between two values. Listed below are the various conditions, and the corresponding formulas using the COUNTIF function. The trick is simply to subtract one condition from the other.
To download a sample spreadsheet containing these COUNTIF formulas using some made-up data, click here: countif-example.xls. This example shows how the formulas can be made a little more dynamic, by concatenating the conditional operator (<, <=, >, >=, <>) with a cell reference.
The data for the following formulas would be contained in column A.| Condition | Formula |
|---|---|
| 1 < x < 4 | =COUNTIF(A:A,"<4")-COUNTIF(A:A,"<=1") |
| 1 <= x < 4 | =COUNTIF(A:A,"<4")-COUNTIF(A:A,"<1") |
| 1 < x <= 4 | =COUNTIF(A:A,"<=4")-COUNTIF(A:A,"<=1") |
| 1 <= x <= 4 | =COUNTIF(A:A,"<=4")-COUNTIF(A:A,"<1") |
More Excel Tips
If you like Excel tips, visit my Excel Tips page, where you can subscribe to my twitter feed, and find other articles I've written. Also, check out my Excel Templates to see various spreadsheet techniques in action.
Note: Sometimes you can get around the use of the COUNTIF function by using an array formula.
References
- Another Method:
MrExcel.com, "Excel CountIf or Sumif with two conditions" http://www.mrexcel.com/td0128.html
