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





PostPosted: Thu Sep 27 14:29:36 PDT 2007 Top

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

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?

Excel304  
 
 
T





PostPosted: Thu Sep 27 14:29:36 PDT 2007 Top

worksheet functions >> COUNTIF a value within a range equals any value in an array/list 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

--
Biff
Microsoft Excel MVP




> 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?


 
 
Harlan





PostPosted: Thu Sep 27 21:54:56 PDT 2007 Top

worksheet functions >> COUNTIF a value within a range equals any value in an array/list
>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





PostPosted: Fri Sep 28 04:55:01 PDT 2007 Top

worksheet functions >> COUNTIF a value within a range equals any value in an array/list 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.




> >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





PostPosted: Fri Sep 28 04:53:01 PDT 2007 Top

worksheet functions >> COUNTIF a value within a range equals any value in an array/list 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.



> 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
>
> --
> Biff
> Microsoft Excel MVP
>
>


> > 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?
>
>
>
 
 
Ron





PostPosted: Fri Sep 28 05:07:30 PDT 2007 Top

worksheet functions >> COUNTIF a value within a range equals any value in an array/list 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)




> 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.
>

>

>> >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





PostPosted: Fri Sep 28 05:11:01 PDT 2007 Top

worksheet functions >> COUNTIF a value within a range equals any value in an array/list 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?




> >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.
>
>
>
 
 
Ron





PostPosted: Fri Sep 28 05:13:23 PDT 2007 Top

worksheet functions >> COUNTIF a value within a range equals any value in an array/list 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)



> 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)
>
>


>> 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.
>>

>>

>>> >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.
>>>
>>>
>>>
>
>


 
 
Roger





PostPosted: Fri Sep 28 05:20:04 PDT 2007 Top

worksheet functions >> COUNTIF a value within a range equals any value in an array/list 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.

--
Regards
Roger Govier





> 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.
>

>

>> >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.
>>
>>
>>


 
 
Roger





PostPosted: Fri Sep 28 07:56:53 PDT 2007 Top

worksheet functions >> COUNTIF a value within a range equals any value in an array/list 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
--
Regards
Roger Govier





> 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?
>

>

>> >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





PostPosted: Fri Sep 28 08:43:03 PDT 2007 Top

worksheet functions >> COUNTIF a value within a range equals any value in an array/list 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.




> 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
> --
> Regards
> Roger Govier
>
>
>


> > 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?
> >

> >

> >> >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.
> >>
> >>
> >>
>
>
>
 
 
Peo





PostPosted: Fri Sep 28 08:53:23 PDT 2007 Top

worksheet functions >> COUNTIF a value within a range equals any value in an array/list It doesn't matter, ISNUMBER in this formula has nothing to do with the
contents of the cells


--


Regards,


Peo Sjoblom





> 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.
>
>

>
>> 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
>> --
>> Regards
>> Roger Govier
>>
>>
>>


>> > 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?
>> >

>> >

>> >> >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





PostPosted: Fri Sep 28 09:03:04 PDT 2007 Top

worksheet functions >> COUNTIF a value within a range equals any value in an array/list Yeah. I figured that out. I'm just an idiot. I forgot to change the column
reference. It all works great!



> It doesn't matter, ISNUMBER in this formula has nothing to do with the
> contents of the cells
>
>
> --
>
>
> Regards,
>
>
> Peo Sjoblom
>
>
>


> > 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.
> >
> >

> >
> >> 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
> >> --
> >> Regards
> >> Roger Govier
> >>
> >>
> >>


> >> > 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?
> >> >

> >> >

> >> >> >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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
 
 
Harlan





PostPosted: Fri Sep 28 09:28:34 PDT 2007 Top

worksheet functions >> COUNTIF a value within a range equals any value in an array/list
>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!D1:D100>180,Sheet2!C1:C100,"")))

or adapt the other array formula

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


 
 
T





PostPosted: Fri Sep 28 15:08:30 PDT 2007 Top

worksheet functions >> COUNTIF a value within a range equals any value in an array/list 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

--
Biff
Microsoft Excel MVP





>>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.
>