| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.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? |
|
#2
Posted to microsoft.public.excel.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 "sweens319" wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF a value within a range equals any value in an array/list
"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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF a value within a range equals any value in an array/li
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. "T. Valko" wrote: 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 "sweens319" wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF a value within a range equals any value in an array/li
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF a value within a range equals any value in an array/li
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? "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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF a value within a range equals any value in an array/li
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) "Ron Coderre" wrote in message ... 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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF a value within a range equals any value in an array/li
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 "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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF a value within a range equals any value in an array/li
Hi
You could go back to Biff's original formula, and add another condition. =SUMPRODUCT(--(ISNUMBER(MATCH(Sheet2!C1:C100,ID,0))), --(Sheet2!$D1:D100180)) assuming your elapsed times are in column D -- Regards Roger Govier "sweens319" wrote in message ... 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? "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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF a value within a range equals any value in an array/li
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. "Roger Govier" wrote: Hi You could go back to Biff's original formula, and add another condition. =SUMPRODUCT(--(ISNUMBER(MATCH(Sheet2!C1:C100,ID,0))), --(Sheet2!$D1:D100180)) assuming your elapsed times are in column D -- Regards Roger Govier "sweens319" wrote in message ... 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? "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. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF a value within a range equals any value in an array/li
It doesn't matter, ISNUMBER in this formula has nothing to do with the
contents of the cells -- Regards, Peo Sjoblom "sweens319" wrote in message ... 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. "Roger Govier" wrote: Hi You could go back to Biff's original formula, and add another condition. =SUMPRODUCT(--(ISNUMBER(MATCH(Sheet2!C1:C100,ID,0))), --(Sheet2!$D1:D100180)) assuming your elapsed times are in column D -- Regards Roger Govier "sweens319" wrote in message ... 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? "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. |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF a value within a range equals any value in an array/li
Yeah. I figured that out. I'm just an idiot. I forgot to change the column
reference. It all works great! "Peo Sjoblom" wrote: It doesn't matter, ISNUMBER in this formula has nothing to do with the contents of the cells -- Regards, Peo Sjoblom "sweens319" wrote in message ... 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. "Roger Govier" wrote: Hi You could go back to Biff's original formula, and add another condition. =SUMPRODUCT(--(ISNUMBER(MATCH(Sheet2!C1:C100,ID,0))), --(Sheet2!$D1:D100180)) assuming your elapsed times are in column D -- Regards Roger Govier "sweens319" wrote in message ... 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? "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. |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF a value within a range equals any value in an array/li
"sweens319" wrote...
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:D100180,Sheet2!C1:C1 00,""))) or adapt the other array formula =COUNT(MATCH(Sheet2!C1:C100,ID,0)/(Sheet2!D1:D100180)) |
|
#15
Posted to microsoft.public.excel.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 "Harlan Grove" wrote in message ... "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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I create a CountIF statement for a range of items in a list | Excel Worksheet Functions | |||
| if specific value from list A equals one of the values from list b... | Excel Worksheet Functions | |||
| if a:a (range) equals january and c:c equals gas then add g:g ($) | Excel Worksheet Functions | |||
| If statement where the logical test is a range that equals a word | Excel Worksheet Functions | |||
| Countif Text equals the same | Excel Discussion (Misc queries) | |||
Linear Mode
