» 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:
- The Accounting Game : Basic Accounting Fresh from the Lemonade Stand
- Rich Dad, Poor Dad: What the Rich Teach Their Kids About Money--That the Poor and Middle Class Do Not!
- Marketing Plans
- Final Accounting: Ambition, Greed and the Fall of Arthur Andersen
- How to Read A Financial Report
- Rich Dad's Guide to Investing: What the Rich Invest in, That the Poor and the Middle Class Do Not!
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