COUNTIF a value within a range equals any value in an array/li
Hi, sweens319
Harlan mentioned that =COUNT(MATCH(Sheet2!C1:C100,ID,0))
is an ARRAY FORMULA.
That means you commit the formula by holding down Ctrl and Shift when you
press Enter, instead of just pressing Enter.
(It's often abbreviated as C+S+E)
When you do that, Excel will but braces around the formula {your_formula}
and it will return the correct value.
Note: You can just type the braces yourself.....you need to let Excel put
them in for you.
--------------------------
Regards,
Ron (XL2003, Win XP)
Microsoft MVP (Excel)
"sweens319" wrote in message
...
Not sure why, but the "faster" one didn't work. I still got a value of
zero.
Thankfully, the original function (sumproduct) worked exactly like I
hoped.
Thank you both for your willingness to help.
"Harlan Grove" wrote:
"T. Valko" wrote...
Try this:
=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet2!C1:C100,ID,0))))
....
If recalc speed isn't essential,
=SUMPRODUCT(COUNTIF(ID,Sheet2!C1:C100))
should return the same result. If recalc speed is essential, I believe
the
array formula
=COUNT(MATCH(Sheet2!C1:C100,ID,0))
would be faster.
|