» Counting values meeting multiple criteria in different columns.
CATEGORY - Excel Formula and Function
VERSION - All Microsoft Excel Versions
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
Book Store:
Recommended Books:
- Keys to Reading an Annual Report (Barron's Business Keys)
- The Total Money Makeover. : A Proven Plan for Financial Fitness
- The McGraw-Hill 36-Hour Course in Finance for Nonfinancial Managers
- Microsoft Outlook Version 2002 Step by Step (With CD-ROM)
- Microsoft Excel 2002 Formulas (With CD-ROM)
- Financial Modeling - 2nd Edition
Doesn't work in Excel 2002?
BlueDaze
I copied everything verbatim into excel but this doesn't work. I have Excel 2002. The returned result is zero, but it looks like the correct answer should be 2. Does this only work in Excel 2003?
Mike_D
Same issue - copied as is and also manipulated quotes but still no joy - what's the g.o.?
Counting values meeting multiple criteria in different columns
oldchippy
The correct formula is
=SUMPRODUCT((A2:C6<1000)*(B2:B6="red"))
oldchippy :)
formula
nobody
it works if you press CTL-ATL-ENTER
=SUMPRODUCT((C2:C6<1000)*(B2:B6="red"))
Ola Sandström