| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to Reference an Array to Count Values?
I need to reference a list of names in a column in one worksheet to count the
number of times those names occur in a column in another worksheet in the same workbook. That is, check the name list and sum the number of times those names occur in another list. How do I do this? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to Reference an Array to Count Values?
Take a look at the sumif function in help.
-- HTH... Jim Thomlinson "Mashuganah" wrote: I need to reference a list of names in a column in one worksheet to count the number of times those names occur in a column in another worksheet in the same workbook. That is, check the name list and sum the number of times those names occur in another list. How do I do this? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to Reference an Array to Count Values?
Thenks Jim. The problem is that the help file does not give an example of
how to structure the equation to compare against multiple text variables. Help gives an example of using a single text variable and gives the impression that I would have to enter each name in the equation which would obviate the use of an array. How do I structure a sumif equation to get the result I need? "Jim Thomlinson" wrote: Take a look at the sumif function in help. -- HTH... Jim Thomlinson "Mashuganah" wrote: I need to reference a list of names in a column in one worksheet to count the number of times those names occur in a column in another worksheet in the same workbook. That is, check the name list and sum the number of times those names occur in another list. How do I do this? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to Reference an Array to Count Values?
Sorry I could have been more clear. In general you will have one countif
function for each name in your source list. You will then just need to sum up all of the individual counts... If there are 10 names in your list of source names then you will need 10 countif functions and one sum function to add all of the counts. -- HTH... Jim Thomlinson "Mashuganah" wrote: Thenks Jim. The problem is that the help file does not give an example of how to structure the equation to compare against multiple text variables. Help gives an example of using a single text variable and gives the impression that I would have to enter each name in the equation which would obviate the use of an array. How do I structure a sumif equation to get the result I need? "Jim Thomlinson" wrote: Take a look at the sumif function in help. -- HTH... Jim Thomlinson "Mashuganah" wrote: I need to reference a list of names in a column in one worksheet to count the number of times those names occur in a column in another worksheet in the same workbook. That is, check the name list and sum the number of times those names occur in another list. How do I do this? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to Reference an Array to Count Values?
Ugh! I was hoping to avoid such a long and difficult to maintain equation.
I've been looking at countifs, lookup, choose, sumifs, match, and sumproduct. I had hoped there was a way to make Excel do the heavy lifting of walking through a list and comparing the values it contains to another list and summing the number of occurrences. "Jim Thomlinson" wrote: Sorry I could have been more clear. In general you will have one countif function for each name in your source list. You will then just need to sum up all of the individual counts... If there are 10 names in your list of source names then you will need 10 countif functions and one sum function to add all of the counts. -- HTH... Jim Thomlinson "Mashuganah" wrote: Thenks Jim. The problem is that the help file does not give an example of how to structure the equation to compare against multiple text variables. Help gives an example of using a single text variable and gives the impression that I would have to enter each name in the equation which would obviate the use of an array. How do I structure a sumif equation to get the result I need? "Jim Thomlinson" wrote: Take a look at the sumif function in help. -- HTH... Jim Thomlinson "Mashuganah" wrote: I need to reference a list of names in a column in one worksheet to count the number of times those names occur in a column in another worksheet in the same workbook. That is, check the name list and sum the number of times those names occur in another list. How do I do this? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to Reference an Array to Count Values?
In one fell swoop is possible but normally I like to post formulas that can
be followed by the average user first. Usually taht covers it. Here is the all in one formula... =COUNT(MATCH(C1:C35, A1:A2, 0)) Which is an array formula and **MUST** be committed with Ctrl+Shift+Enter. If it was committed correctly XL will place { } around the formula for you. A1:A2 is the source list of names C1:C35 is the data list that I want counted. -- HTH... Jim Thomlinson "Mashuganah" wrote: Ugh! I was hoping to avoid such a long and difficult to maintain equation. I've been looking at countifs, lookup, choose, sumifs, match, and sumproduct. I had hoped there was a way to make Excel do the heavy lifting of walking through a list and comparing the values it contains to another list and summing the number of occurrences. "Jim Thomlinson" wrote: Sorry I could have been more clear. In general you will have one countif function for each name in your source list. You will then just need to sum up all of the individual counts... If there are 10 names in your list of source names then you will need 10 countif functions and one sum function to add all of the counts. -- HTH... Jim Thomlinson "Mashuganah" wrote: Thenks Jim. The problem is that the help file does not give an example of how to structure the equation to compare against multiple text variables. Help gives an example of using a single text variable and gives the impression that I would have to enter each name in the equation which would obviate the use of an array. How do I structure a sumif equation to get the result I need? "Jim Thomlinson" wrote: Take a look at the sumif function in help. -- HTH... Jim Thomlinson "Mashuganah" wrote: I need to reference a list of names in a column in one worksheet to count the number of times those names occur in a column in another worksheet in the same workbook. That is, check the name list and sum the number of times those names occur in another list. How do I do this? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to Reference an Array to Count Values?
Thanks Jim! That works!
"Jim Thomlinson" wrote: In one fell swoop is possible but normally I like to post formulas that can be followed by the average user first. Usually that covers it. Here is the all in one formula... =COUNT(MATCH(C1:C35, A1:A2, 0)) Which is an array formula and **MUST** be committed with Ctrl+Shift+Enter. If it was committed correctly XL will place { } around the formula for you. A1:A2 is the source list of names C1:C35 is the data list that I want counted. -- HTH... Jim Thomlinson "Mashuganah" wrote: Ugh! I was hoping to avoid such a long and difficult to maintain equation. I've been looking at countifs, lookup, choose, sumifs, match, and sumproduct. I had hoped there was a way to make Excel do the heavy lifting of walking through a list and comparing the values it contains to another list and summing the number of occurrences. "Jim Thomlinson" wrote: Sorry I could have been more clear. In general you will have one countif function for each name in your source list. You will then just need to sum up all of the individual counts... If there are 10 names in your list of source names then you will need 10 countif functions and one sum function to add all of the counts. -- HTH... Jim Thomlinson "Mashuganah" wrote: Thenks Jim. The problem is that the help file does not give an example of how to structure the equation to compare against multiple text variables. Help gives an example of using a single text variable and gives the impression that I would have to enter each name in the equation which would obviate the use of an array. How do I structure a sumif equation to get the result I need? "Jim Thomlinson" wrote: Take a look at the sumif function in help. -- HTH... Jim Thomlinson "Mashuganah" wrote: I need to reference a list of names in a column in one worksheet to count the number of times those names occur in a column in another worksheet in the same workbook. That is, check the name list and sum the number of times those names occur in another list. How do I do this? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Complex conditional summing - array COUNT works, array SUM gives#VALUE | Excel Worksheet Functions | |||
| Count occurances of equal values in two col. - array within SUMPRO | Excel Worksheet Functions | |||
| Reference array constant values in a formula | Excel Discussion (Misc queries) | |||
| Array: Counting multiple values within array | Excel Worksheet Functions | |||
| Count how many different text values in an array. | Excel Worksheet Functions | |||
Linear Mode
