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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
T. Valko said:
Try this:

=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet2!C1:C100,ID,0))))
Click to expand...
....

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.
 
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.
 
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.
 
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)
 
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?
 
Yikes! Typo!

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

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)
 
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.
 
Hi
You could go back to Biff's original formula, and add another condition.

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

assuming your elapsed times are in column D
 
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!$D1:D100="Yes"
but that returns a zero.
 
It doesn't matter, ISNUMBER in this formula has nothing to do with the
contents of the cells


--


Regards,


Peo Sjoblom
 
Yeah. I figured that out. I'm just an idiot. I forgot to change the column
reference. It all works great!
 
sweens319 said:
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?
Click to expand...
....

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

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

or adapt the other array formula

=COUNT(MATCH(Sheet2!C1:C100,ID,0)/(Sheet2!D1:D100>180))
 
Back
Top