Tip Printed from ExcelTip.com

---

Counting values meeting multiple criteria in different columns.


Problem:

Counting the number of "red" items which cost less than $1000.

Solution:

Using the SUMPRODUCT function to count the number of items meeting the above criteria, as follows:
=SUMPRODUCT((C2:C6<1000)*(B2:B6=""red""))

Item______Item's Color____Price
Jacket____red_____________$500
Jeans_____blue____________$200
T-Shirt___red_____________$1,200
Shoes_____black___________$800
Socks_____red_____________$700

Result 2