counting unique items within categories

[Btaylor64]

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!!

 

[Domenic]

Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF(ItemRange=Item,MATCH("~"&CategoryRange,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!

 

[Btaylor64]

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]

Sorry, I misunderstood. It should be the other way around...

=SUM(IF(FREQUENCY(IF(CategoryRange="Category",MATCH("~"&ItemRange,ItemRan
ge&"",0)),ROW(ItemRange)-MIN(ROW(ItemRange))+1),1))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Teethless mama]

=SUMPRODUCT((CATEGORY="cat A")/COUNTIF(ITEMS,ITEMS&""))

 

[Btaylor64]

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

 

[Btaylor64]

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

 

[Domenic]

Let's assume that A2:A7 contains the item, and B2:B7 contains the
category. Let D24 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!