COUNTIF a value within a range equals any value in an array/list

[sweens319]

I'm not sure if COUNTIF is the correct function, but I've tried many
different functions and combinations of functions but can't seem to get the
result I want.

I have a column that contains multiple user id's from 2 different sites
(with duplicates). **Also, the column is on a separate sheet** I have a
list/array named "ID" that contains only the user ID's from my site.**on the
current sheet** I want to count the number of files my site completed by
searching the values in column C of sheet 2, comparing them to the values in
"ID" and add them.
So, if the value of USER ID is in "ID", add to the counter.

Can anyone help?

 

[T. Valko]

Try this:

=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet2!C1:C100,ID,0))))

Where ID = list/array named "ID" that contains only the user ID's from my
site

 

[Harlan Grove]

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.

 

[sweens319]

That worked perfectly. Thank you so much. I knew there was a reason I keep
the "Discussion Groups Home" in my Favorites list at work. I'm always able to
find or get the answers I need.

 

[sweens319]

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.

 

[Ron Coderre]

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]

Now, say I'm using the first function

=SUMPRODUCT(COUNTIF(ID,Sheet2!C1:C100))

The next column over is a number (elapsed time). How would I change this and
add to it if I want to count all the occurrences of My Site User ID's that
have an elapsed time of >180?

 

[Ron Coderre]

Yikes! Typo!

This

Note: You can just type the braces yourself.....you need to let Excel put
them in for you.

Should be:
Note: You CANNOT just type the braces yourself......you need to let Excel
put
them in for you.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Roger Govier]

Hi

Harlan said the faster formula was an array formula, which needs to be
entered (or amended) using Control+Shift+Enter (CSE), not just Enter.

When you use CSE, Excel will insert curly braces around the formula { }
{=COUNT(MATCH(Sheet2!C1:C100,ID,0))}

I suspect you just used Enter.

 

[Roger Govier]

Hi
You could go back to Biff's original formula, and add another condition.

=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet2!C1:C100,ID,0))),
--(Sheet2!$D1100>180))

assuming your elapsed times are in column D

 

[sweens319]

Awesome! That works for the number column, but what if I have a text column,
like in my other post?

A B
XYZ Yes
XYZ No
ABC No
LMN Yes
ABC Yes
LMN No

I tried ISTEXT with Sheet2!$D1100="Yes"
but that returns a zero.

 

[Peo Sjoblom]

It doesn't matter, ISNUMBER in this formula has nothing to do with the
contents of the cells


--


Regards,


Peo Sjoblom

 

[sweens319]

Yeah. I figured that out. I'm just an idiot. I forgot to change the column
reference. It all works great!

 

[Harlan Grove]

Now, say I'm using the first function

=SUMPRODUCT(COUNTIF(ID,Sheet2!C1:C100))

The next column over is a number (elapsed time). How would I change this
and add to it if I want to count all the occurrences of My Site User ID's
that have an elapsed time of >180?

....

If none of your user IDs would be blank, you could change this to the array
formula

=SUM(COUNTIF(ID,IF(Sheet2!D1100>180,Sheet2!C1:C100,"")))

or adapt the other array formula

=COUNT(MATCH(Sheet2!C1:C100,ID,0)/(Sheet2!D1100>180))

 

[T. Valko]

Out of curiosity I ran some tests.

The array formula is slightly faster than SUMPRODUCT(--(ISNUMBER(MATCH. The
difference may not be significant but the array formula is also shorter.

=SUMPRODUCT(COUNTIF is "significantly" slower than either of the other 2.

http://img67.imageshack.us/img67/5091/calctimes3ki4.jpg

Calculation Timer code by Charles Williams:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx