Tuesday, June 14, 2005

Sum all item prices except one

Todays Tip from Exceltip.com

Problem:
Columns A:B contains clothing items and their matching price.
How could we create a formula that will sum the prices in column B of all the items except the jacket?

Solution:

Use the SUMIF function as follows:
=SUMIF(A2:A6,""<>Jacket"",B2:B6)


Items____Prices
Shoes____100
T-Shirt__20
Jeans____50
Jacket___150
Socks____5

Result 175

Note: This method can be used with the less than < and greater than > keys also for numeric exclusions.