Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting unique items within categories - but EXCLUDING blank cell
Good morning!
About a week ago, I received an amazing formula on this website, for counting the number of unique items within different categories. Specifcally, my problem was: ITEM CATEGORY apple A apple A bannana A bannana B bannana B So I wanted Excel to figure out that there are 2 unique items in Category A ("apple" and "bannana"), and only one in Category B ("bannana"). The solution I was given was: =SUM(IF(FREQUENCY(IF(CategoryRange="Category",MATC H"~"&ItemRange,ItemRange&"",0)),ROW(ItemRange)-MIN(ROW(ItemRange))+1),1)) This is a fantastic solution and it works perfrectly - except that it also counts blank cells as a unique entry, too! Is there a way to keep Excel from counting blank cells as a unique item in each category? Thanks again for the help you guys are providing on this amazing website!! -Brett |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting unique items within categories - but EXCLUDING blank cell
Try...
=SUM(IF(FREQUENCY(IF(CategoryRange="Category",IF(I temRange<"",MATCH("~"& ItemRange,ItemRange&"",0))),ROW(ItemRange)-MIN(ROW(ItemRange))+1),1)) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Btaylor64 wrote: Good morning! About a week ago, I received an amazing formula on this website, for counting the number of unique items within different categories. Specifcally, my problem was: ITEM CATEGORY apple A apple A bannana A bannana B bannana B So I wanted Excel to figure out that there are 2 unique items in Category A ("apple" and "bannana"), and only one in Category B ("bannana"). The solution I was given was: =SUM(IF(FREQUENCY(IF(CategoryRange="Category",MATC H"~"&ItemRange,ItemRange&"", 0)),ROW(ItemRange)-MIN(ROW(ItemRange))+1),1)) This is a fantastic solution and it works perfrectly - except that it also counts blank cells as a unique entry, too! Is there a way to keep Excel from counting blank cells as a unique item in each category? Thanks again for the help you guys are providing on this amazing website!! -Brett |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting unique items within categories - but EXCLUDING blank
Domenic,
Looks like there's a parentheses issue in the formula below? I tried to troubleshoot this using the colored parenthese font that Excel provides, but no luck. All the combinations of parentheses placement I've tried give me errors... Any thoughts? Gratefully, -Brett "Domenic" wrote: Try... =SUM(IF(FREQUENCY(IF(CategoryRange="Category",IF(I temRange<"",MATCH("~"& ItemRange,ItemRange&"",0))),ROW(ItemRange)-MIN(ROW(ItemRange))+1),1)) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Btaylor64 wrote: Good morning! About a week ago, I received an amazing formula on this website, for counting the number of unique items within different categories. Specifcally, my problem was: ITEM CATEGORY apple A apple A bannana A bannana B bannana B So I wanted Excel to figure out that there are 2 unique items in Category A ("apple" and "bannana"), and only one in Category B ("bannana"). The solution I was given was: =SUM(IF(FREQUENCY(IF(CategoryRange="Category",MATC H"~"&ItemRange,ItemRange&"", 0)),ROW(ItemRange)-MIN(ROW(ItemRange))+1),1)) This is a fantastic solution and it works perfrectly - except that it also counts blank cells as a unique entry, too! Is there a way to keep Excel from counting blank cells as a unique item in each category? Thanks again for the help you guys are providing on this amazing website!! -Brett |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting unique items within categories - but EXCLUDING blank
The formula as I posted it looks fine. I tried it just to be sure and
had no problems. Did you copy/paste the formula? Try typing it out manually instead of copy/paste. In article , Btaylor64 wrote: Domenic, Looks like there's a parentheses issue in the formula below? I tried to troubleshoot this using the colored parenthese font that Excel provides, but no luck. All the combinations of parentheses placement I've tried give me errors... Any thoughts? Gratefully, -Brett |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting unique items within categories - but EXCLUDING blank
Domenic,
You're absolutely correct; now it works fine! Not sure what I was doing wrong before. Thanks again for your help! -Brett "Domenic" wrote: The formula as I posted it looks fine. I tried it just to be sure and had no problems. Did you copy/paste the formula? Try typing it out manually instead of copy/paste. In article , Btaylor64 wrote: Domenic, Looks like there's a parentheses issue in the formula below? I tried to troubleshoot this using the colored parenthese font that Excel provides, but no luck. All the combinations of parentheses placement I've tried give me errors... Any thoughts? Gratefully, -Brett |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting unique items within categories - but EXCLUDING blank
You're very welcome! Thanks for the feedback!
In article , Btaylor64 wrote: Domenic, You're absolutely correct; now it works fine! Not sure what I was doing wrong before. Thanks again for your help! -Brett |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting unique items within categories | Excel Worksheet Functions | |||
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 | Excel Discussion (Misc queries) |