Counting entries in a column with a twist

[ltkenbo]

So I making this simple sheet that helps keep track of some products to put it simply. Anyways, I have a column for each product, and another column that is listing whether a certain product is used or unused and based on that I am tallying them at the bottom. Basically the tally cell contains this

Please Login or Register  to view this content.

which will give me the quantity of cells that have text that says "Unused."

Ok here's the twist, there is a corresponding column that asks if a product is defective. So each product not only has a Used/Unused entry, it has a Yes or No if defective entry. What I want to happen is regardless of what is entered in the Used/Unused column, if a "Yes" is in the defective cell, then an entry should not be counted. Now I realize that I could just count the number that are not defective and subtract out "Used" products, but I want this to work without having to enter something into the defective column. Just if someone does, it will override the other used entry column. Hope this makes sense, if you have any questions just ask.

Using excel 2010.

[arthurbr]

Perhaps

Please Login or Register  to view this content.

(supposing defective products in col E

[ltkenbo]

Perhaps

Please Login or Register  to view this content.

(supposing defective products in col E

The only problem with that is that I want it still to still work regardless of whether there is text in the defective column. Because not every product in stock is tested, just whenever is is used. So basically some kind of OR statement that will sum the "unused" cells if a defective cell is "No" OR nothing is in the defective cell.

[arthurbr]

=sumproduct(--(d16:d25="unused"),--(e16:e25<>"yes"))

PLEASE READ MY SIG ABOUT QUOTING THX

[ltkenbo]

Works great thanks! What does the <> operation do exactly? Sorry about quoting.

[arthurbr]

Np.
<> just means " different than". it's a combination of < ( smaller) and > ( larger)

[arthurbr]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save