For AND Conditions
For OR Conditions
This example counts the number of values in the range A1:A10 that fall between 1 and 10, inclusively.
To accomplish this, you can use the following nested IF statement:
The following method also works and is much easier to read if you are conducting multiple tests:
The following method counts the number of dates that fall between two given dates:
- You must enter these formulas as array formulas by pressing CTRL+SHIFT+ENTER simultaneously. On the Macintosh, press COMMAND+RETURN instead.
- Arrays cannot refer to entire columns.
The method shown above counts the number of cells in the range A1:A10 for which both tests evaluate to TRUE. To sum values in corresponding cells (for example, B1:B10), modify the formula as shown below:
You can implement an OR in a SUM+IF statement similarly. To do this, modify the formula shown above by replacing the multiplication sign (*) with a plus sign (+). This gives the following generic formula: