» 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:
- Rich Dad, Poor Dad: What the Rich Teach Their Kids About Money--That the Poor and Middle Class Do Not!
- Business Plans Kit for Dummies (With CD-ROM)
- Your First Business Plan: A Simple Question and Answer Format Designed to Help You Write Your Own Plan (3rd Ed)
- Wall Street Journal Guide to Understanding Money and Investing
- Financial Statement Analysis with S&P insert card
- Lower Your Taxes - Big Time! : Wealth-Building, Tax Reduction Secrets from an IRS Insider
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