Count number of cells based on multiple criteria

Originally Posted by Cumberland

Please can someone help me with a counting issue?

Please Login or Register  to view this content.

What is the formula I need to count the number of cells that contain "APPLES" AND "FRED"? Answer as above should be 3. Think I need a SUMPRODUCT formula, but can't come up with it.

Thanks!

Hi,


=SumProduct(--(A1:A9="APPLES")*(--(B1:B9="FRED")))

should give you 3

---

Originally Posted by Cumberland

Thanks Brian

?

.

Can you just tell me the significance of the double-negative in the formula? I've seen this a couple of times recently and don't know what it means:

=SumProduct(--(A1:A9="APPLES")*(--(B1:B9="FRED")))

hi

its known as a double unary - and is best described by the SumProduct expert at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

basically, it takes the 'True' - 'False' reply and forces it to 0 or 1 by *-1 *-1

Excel diddles with True/0 and False/1 to suit it's needs, this just forces it to be numeric.

---

Originally Posted by Cumberland

Thanks again Bryan (apologies for the mis-spelling before)!

np - it happens often
---