Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting unique items within categories
Hi, I've run into a little problem that I can't seem to figure out...
I have two columns of data - one column contains a list of items, and the other contains one of three possible categories (A, B, or C) to which the items are assigned. Due to other considerations within my sheet, specific items may be repeated multiple times within the first column, and they may also exist in any combination of all three categories. My question is: Is there a way to summarize how many UNIQUE items exist within each of the three categories (i.e., the number of different items within category A, not including the repeats)? I've tried lots of versions of the countif and frequency function, but nothing has worked so far... thanks for your help!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting unique items within categories
Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER... =SUM(IF(FREQUENCY(IF(ItemRange=Item,MATCH("~"&Cate goryRange,CategoryRange &"",0)),ROW(CategoryRange)-MIN(ROW(CategoryRange))+1),1)) If the items listed in the range are text values, enclosed the criterion for the range within quotes. In other words, replace... =Item with ="Item" Hope this helps! In article , Btaylor64 wrote: Hi, I've run into a little problem that I can't seem to figure out... I have two columns of data - one column contains a list of items, and the other contains one of three possible categories (A, B, or C) to which the items are assigned. Due to other considerations within my sheet, specific items may be repeated multiple times within the first column, and they may also exist in any combination of all three categories. My question is: Is there a way to summarize how many UNIQUE items exist within each of the three categories (i.e., the number of different items within category A, not including the repeats)? I've tried lots of versions of the countif and frequency function, but nothing has worked so far... thanks for your help!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting unique items within categories
Domenic,
Thanks for the fast reply!! I believe that the formula you've suggested would require a specific new formula cell for each different item name, and there is a great variety of them... For clarification, what I've got looks like this: ITEMS CATEGORY AP-0014 cat A AC-9999 cat B SD-5235 cat A AC-1212 cat C AP-0014 cat A AC-1212 cat C So for example, without having to know the names of the items in advance, I want to count the number of unique items in category A. The answer would be 2 (AP-0012 is a "repeat", and so would only be counted once). Seems simple, but I'm just not getting there... Thank you enormously for your continued support!! -Brett "Domenic" wrote: Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER... =SUM(IF(FREQUENCY(IF(ItemRange=Item,MATCH("~"&Cate goryRange,CategoryRange &"",0)),ROW(CategoryRange)-MIN(ROW(CategoryRange))+1),1)) If the items listed in the range are text values, enclosed the criterion for the range within quotes. In other words, replace... =Item with ="Item" Hope this helps! In article , Btaylor64 wrote: Hi, I've run into a little problem that I can't seem to figure out... I have two columns of data - one column contains a list of items, and the other contains one of three possible categories (A, B, or C) to which the items are assigned. Due to other considerations within my sheet, specific items may be repeated multiple times within the first column, and they may also exist in any combination of all three categories. My question is: Is there a way to summarize how many UNIQUE items exist within each of the three categories (i.e., the number of different items within category A, not including the repeats)? I've tried lots of versions of the countif and frequency function, but nothing has worked so far... thanks for your help!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting unique items within categories
Sorry, I misunderstood. It should be the other way around...
=SUM(IF(FREQUENCY(IF(CategoryRange="Category",MATC H("~"&ItemRange,ItemRan ge&"",0)),ROW(ItemRange)-MIN(ROW(ItemRange))+1),1)) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Btaylor64 wrote: Domenic, Thanks for the fast reply!! I believe that the formula you've suggested would require a specific new formula cell for each different item name, and there is a great variety of them... For clarification, what I've got looks like this: ITEMS CATEGORY AP-0014 cat A AC-9999 cat B SD-5235 cat A AC-1212 cat C AP-0014 cat A AC-1212 cat C So for example, without having to know the names of the items in advance, I want to count the number of unique items in category A. The answer would be 2 (AP-0012 is a "repeat", and so would only be counted once). Seems simple, but I'm just not getting there... Thank you enormously for your continued support!! -Brett |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting unique items within categories
=SUMPRODUCT((CATEGORY="cat A")/COUNTIF(ITEMS,ITEMS&""))
"Btaylor64" wrote: Hi, I've run into a little problem that I can't seem to figure out... I have two columns of data - one column contains a list of items, and the other contains one of three possible categories (A, B, or C) to which the items are assigned. Due to other considerations within my sheet, specific items may be repeated multiple times within the first column, and they may also exist in any combination of all three categories. My question is: Is there a way to summarize how many UNIQUE items exist within each of the three categories (i.e., the number of different items within category A, not including the repeats)? I've tried lots of versions of the countif and frequency function, but nothing has worked so far... thanks for your help!! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting unique items within categories
Domenic, I really appreciate your help!
When you use the terms ItemRange and CategoryRange in the formula below, do you mean (using my example below), A1:A6 and B1:B6, respectively? When Excel gets to the second IF in this formula, it attempts to evaluate whether B1:B6 = "Cat A", which seems to be a syntax error? I get "#VALUE!" as a response. When I click to show the calculation steps, this ItemRange after the first IF is the part where it gives me an error. Am I just misunderstanding your intent as to what ItemRange means? Again, I am so grateful for your help! -Brett "Domenic" wrote: Sorry, I misunderstood. It should be the other way around... =SUM(IF(FREQUENCY(IF(CategoryRange="Category",MATC H("~"&ItemRange,ItemRan ge&"",0)),ROW(ItemRange)-MIN(ROW(ItemRange))+1),1)) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Btaylor64 wrote: Domenic, Thanks for the fast reply!! I believe that the formula you've suggested would require a specific new formula cell for each different item name, and there is a great variety of them... For clarification, what I've got looks like this: ITEMS CATEGORY AP-0014 cat A AC-9999 cat B SD-5235 cat A AC-1212 cat C AP-0014 cat A AC-1212 cat C So for example, without having to know the names of the items in advance, I want to count the number of unique items in category A. The answer would be 2 (AP-0012 is a "repeat", and so would only be counted once). Seems simple, but I'm just not getting there... Thank you enormously for your continued support!! -Brett |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting unique items within categories
Teethless Mama,
I appreciate your help - when you use the term CATEGORY in the formula below, does that mean the range of cells that contain my categories (ie, B2:B5)? And the same with ITEMS (A2:A5)? This is where my error messages are flagging. For clarification, my spreadsheet is: ITEMS CATEGORY apple A bannana B bannana B apple B What I'm looking for is a way to count only the number of unique items within each category (A, B, and C) - for example, there are 2 unique items within category B. For some reason, neither the formula you very kindly offered, nor the one offered by Domenic earlier today, are working for me. I don't get any values at all as an answer, only error statements. (I am remembering to use ctrl+shift+enter). Any thoughts? I do greatly appreciate your time and expertise! -Brett "Teethless mama" wrote: =SUMPRODUCT((CATEGORY="cat A")/COUNTIF(ITEMS,ITEMS&"")) "Btaylor64" wrote: Hi, I've run into a little problem that I can't seem to figure out... I have two columns of data - one column contains a list of items, and the other contains one of three possible categories (A, B, or C) to which the items are assigned. Due to other considerations within my sheet, specific items may be repeated multiple times within the first column, and they may also exist in any combination of all three categories. My question is: Is there a way to summarize how many UNIQUE items exist within each of the three categories (i.e., the number of different items within category A, not including the repeats)? I've tried lots of versions of the countif and frequency function, but nothing has worked so far... thanks for your help!! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting unique items within categories
Let's assume that A2:A7 contains the item, and B2:B7 contains the
category. Let D2:D4 contain 'cat a', 'cat b', and 'cat c', then enter the following formula in E2, confirm with CONTROL+SHIFT+ENTER (if done correctly, Excel will automatically place curly braces {.....} around the formula), and copy down: =SUM(IF(FREQUENCY(IF($B$2:$B$7=D2,MATCH("~"&$A$2:$ A$7,$A$2:$A$7&"",0)),RO W($A$2:$A$7)-ROW($A$2)+1),1)) Hope this helps! In article , Btaylor64 wrote: Domenic, I really appreciate your help! When you use the terms ItemRange and CategoryRange in the formula below, do you mean (using my example below), A1:A6 and B1:B6, respectively? When Excel gets to the second IF in this formula, it attempts to evaluate whether B1:B6 = "Cat A", which seems to be a syntax error? I get "#VALUE!" as a response. When I click to show the calculation steps, this ItemRange after the first IF is the part where it gives me an error. Am I just misunderstanding your intent as to what ItemRange means? Again, I am so grateful for your help! -Brett |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Unique Items with Multiple Criteria | Excel Worksheet Functions | |||
Counting Unique Items with Multiple Criteria | Excel Worksheet Functions | |||
Counting unique items...please help | Excel Worksheet Functions | |||
counting unique items(values or text) | Excel Worksheet Functions | |||
counting unique items | Excel Discussion (Misc queries) |