Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula looking up multiple information.
Excel 2003. I'm wanting a formula that encompasses all 3 requirements:
1. If a number in column A appears multiple times in column B. 2. Then if 2 different pre-determinded names appear in column C (there's one name per cell) that are in the same rows as the numbers found in column B. 3. Bring back the count of times the number appears in column B, less the number of times the 2 names appear in column C (if only 1 name appears, just the total count of numbers found in column B) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula looking up multiple information.
Here's an example of what I'm trying to do:
ColA ColB ColC ColD L1 15 15 tin L2 15 plea L3 15 tin L4 19 19 tin L5 19 19 serial L6 22 22 tin I would like to know how many items in column c there are for each number in column A. If however tin and plea appear in the same group, they would only count as 1. "Tamara" wrote: Excel 2003. I'm wanting a formula that encompasses all 3 requirements: 1. If a number in column A appears multiple times in column B. 2. Then if 2 different pre-determinded names appear in column C (there's one name per cell) that are in the same rows as the numbers found in column B. 3. Bring back the count of times the number appears in column B, less the number of times the 2 names appear in column C (if only 1 name appears, just the total count of numbers found in column B) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula looking up multiple information.
Hi!
I would like to know how many items in column c there are for each number in column A. If however tin and plea appear in the same group, they would only count as 1. In other words, you want to count the unique values for each group. You need to have a "group" number associated with each entry in column C so you'd base this formula on the "groups" in column B, not column A. List the unique group numbers: A10 = 15 A11 = 19 A12 = 22 Enter this formula in B10 as an array using the key combo of CTRL,SHIFT,ENTER: =SUM(N(FREQUENCY(IF(B$2:B$7=A10,MATCH(C$2:C$7,C$2: C$7,0)),MATCH(C$2:C$7,C$2:C$7,0))0)) Copy down to B12 Biff "Tamara" wrote in message ... Excel 2003. I'm wanting a formula that encompasses all 3 requirements: 1. If a number in column A appears multiple times in column B. 2. Then if 2 different pre-determinded names appear in column C (there's one name per cell) that are in the same rows as the numbers found in column B. 3. Bring back the count of times the number appears in column B, less the number of times the 2 names appear in column C (if only 1 name appears, just the total count of numbers found in column B) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula looking up multiple information.
Hi,
Thanks for that ... With the formula though, I'm trying to make it read so if only the words "tin" and "plea" are in the same group they're to be counted as one. If they appear in a group with a different text name they are to be counted individually. ie If the product group had: tin, tin, serial this would count as 3 tin, plea, serial this would count as 2 (as tin and plea are in the same group) plea, plea count as 2 and so on ..... Is there a formula for this? "Biff" wrote: Hi! I would like to know how many items in column c there are for each number in column A. If however tin and plea appear in the same group, they would only count as 1. In other words, you want to count the unique values for each group. You need to have a "group" number associated with each entry in column C so you'd base this formula on the "groups" in column B, not column A. List the unique group numbers: A10 = 15 A11 = 19 A12 = 22 Enter this formula in B10 as an array using the key combo of CTRL,SHIFT,ENTER: =SUM(N(FREQUENCY(IF(B$2:B$7=A10,MATCH(C$2:C$7,C$2: C$7,0)),MATCH(C$2:C$7,C$2:C$7,0))0)) Copy down to B12 Biff "Tamara" wrote in message ... Excel 2003. I'm wanting a formula that encompasses all 3 requirements: 1. If a number in column A appears multiple times in column B. 2. Then if 2 different pre-determinded names appear in column C (there's one name per cell) that are in the same rows as the numbers found in column B. 3. Bring back the count of times the number appears in column B, less the number of times the 2 names appear in column C (if only 1 name appears, just the total count of numbers found in column B) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula looking up multiple information.
Sorry, I don't understand what you're trying to do.
Biff "Tamara" wrote in message ... Hi, Thanks for that ... With the formula though, I'm trying to make it read so if only the words "tin" and "plea" are in the same group they're to be counted as one. If they appear in a group with a different text name they are to be counted individually. ie If the product group had: tin, tin, serial this would count as 3 tin, plea, serial this would count as 2 (as tin and plea are in the same group) plea, plea count as 2 and so on ..... Is there a formula for this? "Biff" wrote: Hi! I would like to know how many items in column c there are for each number in column A. If however tin and plea appear in the same group, they would only count as 1. In other words, you want to count the unique values for each group. You need to have a "group" number associated with each entry in column C so you'd base this formula on the "groups" in column B, not column A. List the unique group numbers: A10 = 15 A11 = 19 A12 = 22 Enter this formula in B10 as an array using the key combo of CTRL,SHIFT,ENTER: =SUM(N(FREQUENCY(IF(B$2:B$7=A10,MATCH(C$2:C$7,C$2: C$7,0)),MATCH(C$2:C$7,C$2:C$7,0))0)) Copy down to B12 Biff "Tamara" wrote in message ... Excel 2003. I'm wanting a formula that encompasses all 3 requirements: 1. If a number in column A appears multiple times in column B. 2. Then if 2 different pre-determinded names appear in column C (there's one name per cell) that are in the same rows as the numbers found in column B. 3. Bring back the count of times the number appears in column B, less the number of times the 2 names appear in column C (if only 1 name appears, just the total count of numbers found in column B) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
If formula that looks at multiple cells and values, and then calul | Excel Discussion (Misc queries) | |||
Formula to compare multiple rows values based on another column? | Excel Worksheet Functions | |||
Formula to find information between 2 dates | Excel Worksheet Functions | |||
Excel should let me refer to the same formula on multiple sheets | Excel Worksheet Functions |