Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using sum(1/countif....) not returning expected result
I have a spreadsheet where I am trying to count the number of unique values
in Column A, based on set criteria in Column B. I have searched all over the internet and the most popular solution seems to be the formula below. For examples purposes assume the following: A B 1 X 2 Y 2 X 3 Y 3 Y 4 X 4 X 4 X I use the following array formula to count the number of unique numbers in column A, based on (X) in column B. =SUM(1/COUNTIF(A1:A8,A1:A8)*(B1:B8="x")) I would expect the result to be 3, but instead I get 2.5 Any suggestions on how to correct my formula? My actual spreadsheet includes both numeric and alpha fields that need to be counted, but no blank spaces. Thanks in advance for your assistance, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using sum(1/countif....) not returning expected result
There are 4 unique values in your example of column A:
you can count them with array formula: =SUM(COUNTIF(A1:A10,A1:A10)/COUNTIF(A1:A10,A1:A10)^2) but i think you need to describe exactly what you mean by "..based on set criteria in Column B.." "Kent (thanks)" wrote in message ... I have a spreadsheet where I am trying to count the number of unique values in Column A, based on set criteria in Column B. I have searched all over the internet and the most popular solution seems to be the formula below. For examples purposes assume the following: A B 1 X 2 Y 2 X 3 Y 3 Y 4 X 4 X 4 X I use the following array formula to count the number of unique numbers in column A, based on (X) in column B. =SUM(1/COUNTIF(A1:A8,A1:A8)*(B1:B8="x")) I would expect the result to be 3, but instead I get 2.5 Any suggestions on how to correct my formula? My actual spreadsheet includes both numeric and alpha fields that need to be counted, but no blank spaces. Thanks in advance for your assistance, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using sum(1/countif....) not returning expected result
You are correct, there are 4 unique values in column A, but I only want them
to be counted if there is a corresponding (X) in column B. Thus, based on my example, "3" should not be counted as a unique value as there is no corresponding "X" in column B. "JethroUK©" wrote: There are 4 unique values in your example of column A: you can count them with array formula: =SUM(COUNTIF(A1:A10,A1:A10)/COUNTIF(A1:A10,A1:A10)^2) but i think you need to describe exactly what you mean by "..based on set criteria in Column B.." "Kent (thanks)" wrote in message ... I have a spreadsheet where I am trying to count the number of unique values in Column A, based on set criteria in Column B. I have searched all over the internet and the most popular solution seems to be the formula below. For examples purposes assume the following: A B 1 X 2 Y 2 X 3 Y 3 Y 4 X 4 X 4 X I use the following array formula to count the number of unique numbers in column A, based on (X) in column B. =SUM(1/COUNTIF(A1:A8,A1:A8)*(B1:B8="x")) I would expect the result to be 3, but instead I get 2.5 Any suggestions on how to correct my formula? My actual spreadsheet includes both numeric and alpha fields that need to be counted, but no blank spaces. Thanks in advance for your assistance, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using sum(1/countif....) not returning expected result
=SUM(--(FREQUENCY(IF(B1:B10="X",MATCH(A1:A10,A1:A10,0)),R OW(INDIRECT("1:"&RO
WS(A1:A10))))0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Kent (thanks)" wrote in message ... You are correct, there are 4 unique values in column A, but I only want them to be counted if there is a corresponding (X) in column B. Thus, based on my example, "3" should not be counted as a unique value as there is no corresponding "X" in column B. "JethroUK©" wrote: There are 4 unique values in your example of column A: you can count them with array formula: =SUM(COUNTIF(A1:A10,A1:A10)/COUNTIF(A1:A10,A1:A10)^2) but i think you need to describe exactly what you mean by "..based on set criteria in Column B.." "Kent (thanks)" wrote in message ... I have a spreadsheet where I am trying to count the number of unique values in Column A, based on set criteria in Column B. I have searched all over the internet and the most popular solution seems to be the formula below. For examples purposes assume the following: A B 1 X 2 Y 2 X 3 Y 3 Y 4 X 4 X 4 X I use the following array formula to count the number of unique numbers in column A, based on (X) in column B. =SUM(1/COUNTIF(A1:A8,A1:A8)*(B1:B8="x")) I would expect the result to be 3, but instead I get 2.5 Any suggestions on how to correct my formula? My actual spreadsheet includes both numeric and alpha fields that need to be counted, but no blank spaces. Thanks in advance for your assistance, |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using sum(1/countif....) not returning expected result
Assuming that A2:B9 contains the data, try the following formula, which
needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =COUNT(1/FREQUENCY(IF(B2:B9="X",IF(A2:A9<"",A2:A9)),IF(B2: B9="X",IF(A2:A 9<"",A2:A9)))) Hope this helps! In article , Kent (thanks) wrote: I have a spreadsheet where I am trying to count the number of unique values in Column A, based on set criteria in Column B. I have searched all over the internet and the most popular solution seems to be the formula below. For examples purposes assume the following: A B 1 X 2 Y 2 X 3 Y 3 Y 4 X 4 X 4 X I use the following array formula to count the number of unique numbers in column A, based on (X) in column B. =SUM(1/COUNTIF(A1:A8,A1:A8)*(B1:B8="x")) I would expect the result to be 3, but instead I get 2.5 Any suggestions on how to correct my formula? My actual spreadsheet includes both numeric and alpha fields that need to be counted, but no blank spaces. Thanks in advance for your assistance, |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using sum(1/countif....) not returning expected result
Another variation, also array entered. I got it by piecing together what I
could remember from one of Harlan's posts. =SUM(--(FREQUENCY(IF(B1:B10="X",A1:A10,""),IF(B1:B10="X", A1:A10,""))0)) "Bob Phillips" wrote: =SUM(--(FREQUENCY(IF(B1:B10="X",MATCH(A1:A10,A1:A10,0)),R OW(INDIRECT("1:"&RO WS(A1:A10))))0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Kent (thanks)" wrote in message ... You are correct, there are 4 unique values in column A, but I only want them to be counted if there is a corresponding (X) in column B. Thus, based on my example, "3" should not be counted as a unique value as there is no corresponding "X" in column B. "JethroUK©" wrote: There are 4 unique values in your example of column A: you can count them with array formula: =SUM(COUNTIF(A1:A10,A1:A10)/COUNTIF(A1:A10,A1:A10)^2) but i think you need to describe exactly what you mean by "..based on set criteria in Column B.." "Kent (thanks)" wrote in message ... I have a spreadsheet where I am trying to count the number of unique values in Column A, based on set criteria in Column B. I have searched all over the internet and the most popular solution seems to be the formula below. For examples purposes assume the following: A B 1 X 2 Y 2 X 3 Y 3 Y 4 X 4 X 4 X I use the following array formula to count the number of unique numbers in column A, based on (X) in column B. =SUM(1/COUNTIF(A1:A8,A1:A8)*(B1:B8="x")) I would expect the result to be 3, but instead I get 2.5 Any suggestions on how to correct my formula? My actual spreadsheet includes both numeric and alpha fields that need to be counted, but no blank spaces. Thanks in advance for your assistance, |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using sum(1/countif....) not returning expected result
Although I noticed that it only works if A1:A10 is numeric, while Bob's works
on both numbers and text. "JMB" wrote: Another variation, also array entered. I got it by piecing together what I could remember from one of Harlan's posts. =SUM(--(FREQUENCY(IF(B1:B10="X",A1:A10,""),IF(B1:B10="X", A1:A10,""))0)) "Bob Phillips" wrote: =SUM(--(FREQUENCY(IF(B1:B10="X",MATCH(A1:A10,A1:A10,0)),R OW(INDIRECT("1:"&RO WS(A1:A10))))0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Kent (thanks)" wrote in message ... You are correct, there are 4 unique values in column A, but I only want them to be counted if there is a corresponding (X) in column B. Thus, based on my example, "3" should not be counted as a unique value as there is no corresponding "X" in column B. "JethroUK©" wrote: There are 4 unique values in your example of column A: you can count them with array formula: =SUM(COUNTIF(A1:A10,A1:A10)/COUNTIF(A1:A10,A1:A10)^2) but i think you need to describe exactly what you mean by "..based on set criteria in Column B.." "Kent (thanks)" wrote in message ... I have a spreadsheet where I am trying to count the number of unique values in Column A, based on set criteria in Column B. I have searched all over the internet and the most popular solution seems to be the formula below. For examples purposes assume the following: A B 1 X 2 Y 2 X 3 Y 3 Y 4 X 4 X 4 X I use the following array formula to count the number of unique numbers in column A, based on (X) in column B. =SUM(1/COUNTIF(A1:A8,A1:A8)*(B1:B8="x")) I would expect the result to be 3, but instead I get 2.5 Any suggestions on how to correct my formula? My actual spreadsheet includes both numeric and alpha fields that need to be counted, but no blank spaces. Thanks in advance for your assistance, |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using sum(1/countif....) not returning expected result
Bob,
I have used the formula you indicated below. In my spreadsheet, the actual data that I want to count is located in cells B10:B82 with my "X"s being located in cells R10:R82. When I plug in these cell ranges into the formula below, I get an error in that it does not appear to like the (indirect("1: Any ideas? Thanks, Kent "Bob Phillips" wrote: =SUM(--(FREQUENCY(IF(B1:B10="X",MATCH(A1:A10,A1:A10,0)),R OW(INDIRECT("1:"&RO WS(A1:A10))))0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Kent (thanks)" wrote in message ... You are correct, there are 4 unique values in column A, but I only want them to be counted if there is a corresponding (X) in column B. Thus, based on my example, "3" should not be counted as a unique value as there is no corresponding "X" in column B. "JethroUK©" wrote: There are 4 unique values in your example of column A: you can count them with array formula: =SUM(COUNTIF(A1:A10,A1:A10)/COUNTIF(A1:A10,A1:A10)^2) but i think you need to describe exactly what you mean by "..based on set criteria in Column B.." "Kent (thanks)" wrote in message ... I have a spreadsheet where I am trying to count the number of unique values in Column A, based on set criteria in Column B. I have searched all over the internet and the most popular solution seems to be the formula below. For examples purposes assume the following: A B 1 X 2 Y 2 X 3 Y 3 Y 4 X 4 X 4 X I use the following array formula to count the number of unique numbers in column A, based on (X) in column B. =SUM(1/COUNTIF(A1:A8,A1:A8)*(B1:B8="x")) I would expect the result to be 3, but instead I get 2.5 Any suggestions on how to correct my formula? My actual spreadsheet includes both numeric and alpha fields that need to be counted, but no blank spaces. Thanks in advance for your assistance, |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using sum(1/countif....) not returning expected result
Bob,
I found my error. Your formula works great! Thanks for the help! Kent "Kent (thanks)" wrote: Bob, I have used the formula you indicated below. In my spreadsheet, the actual data that I want to count is located in cells B10:B82 with my "X"s being located in cells R10:R82. When I plug in these cell ranges into the formula below, I get an error in that it does not appear to like the (indirect("1: Any ideas? Thanks, Kent "Bob Phillips" wrote: =SUM(--(FREQUENCY(IF(B1:B10="X",MATCH(A1:A10,A1:A10,0)),R OW(INDIRECT("1:"&RO WS(A1:A10))))0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Kent (thanks)" wrote in message ... You are correct, there are 4 unique values in column A, but I only want them to be counted if there is a corresponding (X) in column B. Thus, based on my example, "3" should not be counted as a unique value as there is no corresponding "X" in column B. "JethroUK©" wrote: There are 4 unique values in your example of column A: you can count them with array formula: =SUM(COUNTIF(A1:A10,A1:A10)/COUNTIF(A1:A10,A1:A10)^2) but i think you need to describe exactly what you mean by "..based on set criteria in Column B.." "Kent (thanks)" wrote in message ... I have a spreadsheet where I am trying to count the number of unique values in Column A, based on set criteria in Column B. I have searched all over the internet and the most popular solution seems to be the formula below. For examples purposes assume the following: A B 1 X 2 Y 2 X 3 Y 3 Y 4 X 4 X 4 X I use the following array formula to count the number of unique numbers in column A, based on (X) in column B. =SUM(1/COUNTIF(A1:A8,A1:A8)*(B1:B8="x")) I would expect the result to be 3, but instead I get 2.5 Any suggestions on how to correct my formula? My actual spreadsheet includes both numeric and alpha fields that need to be counted, but no blank spaces. Thanks in advance for your assistance, |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using sum(1/countif....) not returning expected result
NG wrap-around caused it I presume?
-- HTH Bob Phillips (remove xxx from email address if mailing direct) "Kent (thanks)" wrote in message ... Bob, I found my error. Your formula works great! Thanks for the help! Kent "Kent (thanks)" wrote: Bob, I have used the formula you indicated below. In my spreadsheet, the actual data that I want to count is located in cells B10:B82 with my "X"s being located in cells R10:R82. When I plug in these cell ranges into the formula below, I get an error in that it does not appear to like the (indirect("1: Any ideas? Thanks, Kent "Bob Phillips" wrote: =SUM(--(FREQUENCY(IF(B1:B10="X",MATCH(A1:A10,A1:A10,0)),R OW(INDIRECT("1:"&RO WS(A1:A10))))0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Kent (thanks)" wrote in message ... You are correct, there are 4 unique values in column A, but I only want them to be counted if there is a corresponding (X) in column B. Thus, based on my example, "3" should not be counted as a unique value as there is no corresponding "X" in column B. "JethroUK©" wrote: There are 4 unique values in your example of column A: you can count them with array formula: =SUM(COUNTIF(A1:A10,A1:A10)/COUNTIF(A1:A10,A1:A10)^2) but i think you need to describe exactly what you mean by "..based on set criteria in Column B.." "Kent (thanks)" wrote in message ... I have a spreadsheet where I am trying to count the number of unique values in Column A, based on set criteria in Column B. I have searched all over the internet and the most popular solution seems to be the formula below. For examples purposes assume the following: A B 1 X 2 Y 2 X 3 Y 3 Y 4 X 4 X 4 X I use the following array formula to count the number of unique numbers in column A, based on (X) in column B. =SUM(1/COUNTIF(A1:A8,A1:A8)*(B1:B8="x")) I would expect the result to be 3, but instead I get 2.5 Any suggestions on how to correct my formula? My actual spreadsheet includes both numeric and alpha fields that need to be counted, but no blank spaces. Thanks in advance for your assistance, |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using sum(1/countif....) not returning expected result
Just a typo on my part, inserted an extra " where I did not need it
"Bob Phillips" wrote: NG wrap-around caused it I presume? -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Kent (thanks)" wrote in message ... Bob, I found my error. Your formula works great! Thanks for the help! Kent "Kent (thanks)" wrote: Bob, I have used the formula you indicated below. In my spreadsheet, the actual data that I want to count is located in cells B10:B82 with my "X"s being located in cells R10:R82. When I plug in these cell ranges into the formula below, I get an error in that it does not appear to like the (indirect("1: Any ideas? Thanks, Kent "Bob Phillips" wrote: =SUM(--(FREQUENCY(IF(B1:B10="X",MATCH(A1:A10,A1:A10,0)),R OW(INDIRECT("1:"&RO WS(A1:A10))))0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Kent (thanks)" wrote in message ... You are correct, there are 4 unique values in column A, but I only want them to be counted if there is a corresponding (X) in column B. Thus, based on my example, "3" should not be counted as a unique value as there is no corresponding "X" in column B. "JethroUK©" wrote: There are 4 unique values in your example of column A: you can count them with array formula: =SUM(COUNTIF(A1:A10,A1:A10)/COUNTIF(A1:A10,A1:A10)^2) but i think you need to describe exactly what you mean by "..based on set criteria in Column B.." "Kent (thanks)" wrote in message ... I have a spreadsheet where I am trying to count the number of unique values in Column A, based on set criteria in Column B. I have searched all over the internet and the most popular solution seems to be the formula below. For examples purposes assume the following: A B 1 X 2 Y 2 X 3 Y 3 Y 4 X 4 X 4 X I use the following array formula to count the number of unique numbers in column A, based on (X) in column B. =SUM(1/COUNTIF(A1:A8,A1:A8)*(B1:B8="x")) I would expect the result to be 3, but instead I get 2.5 Any suggestions on how to correct my formula? My actual spreadsheet includes both numeric and alpha fields that need to be counted, but no blank spaces. Thanks in advance for your assistance, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"1235" appears as "One thousand two hundred thirty five" | Excel Discussion (Misc queries) | |||
Adding "and" to Spellnumber code | Excel Discussion (Misc queries) | |||
EXCEL:NUMBER TO GREEK WORDS | Excel Worksheet Functions | |||
Amount or Numbers in Words | New Users to Excel | |||
Spellnumber | Excel Worksheet Functions |