Count unique values with criteria

Hi,

I want to return the number of unique values with criteria, as a number. I am trying to find how many different items are in a container. The value I am using as the match is on sheet 1 - A and i want to return the value in B. Sheet 2 contains the data I am searching through eg.

1
MSCU
PONU
APHU

2
A MSCU
B MSCU
A MSCU
A MSCU
B PONU
B PONU
C APHU
B APHU
C APHU
A APHU
B APHU

What I want to return in sheet 1 is:
MSCU 2
PONU 1
APHU 3

With the container number being the criteria of which to search. What I have been using a countif but this does not return unique values. Basically I am after a countif with unique values.

Any help is greatly appreciated.

Thanks

it's not entirely clear to me how sheet 2 is laid out... if we're saying the values in column A are say

A APHU etc...

Then I would myself be inclined to add a helper column to be on sheet2 which will in turn permit you to avoid use of expensive array formulae.

If using your example A MSCU appears in A2 on Sheet2 (ensure you have 1 row above first record) in B2:

=1-N(ISNUMBER(MATCH($A2,$A$1:$A1,0)))*(1-COUNTBLANK($A2))

Copy this down for all records.

Now on Sheet 1 you can use a standard SUMIF

Where A1 = "MSCU", B1:

=SUMIF('2'!$A$2:$A$100,"* "&$A1,'2'!$B$2:$B$100)

If this is not your layout I'd advise you upload a sample of your file.

Use a pivot table.

If you post an example I can show you how.

HTH

Thanks a lot for your help everyone.

Donkeyote - I don't entirely understand what references you are trying to make to what part of the formula, so I have added a basic format of what I have.

In sheet1 B3-B6, I want to return the number of unique values, based on the criteria of container number (A3-A6). Please note that I have manually input the values you see, to give you a better idea of what I am trying to achieve.

The values I have manually put in column B is the number of unique items in a container. The list of items with their respective container number are on sheet 2.

I don't want to use a pivot table as I'm not entirely sure how to and I have 500+ lines, will I have to modify this data at all? Also, I want to return this into a different sheet than the data.

Thanks for any help

benno87,

use a helper cell in C on sheet2 to concatenate A & B, eg =A3&":"&B3, copy down for all rows.

now on sheet1 using rwgrietveld sumproduct approach, your formula for Sheet1 B3 etc...

=SUMPRODUCT(--(1/COUNTIF(Sheet2!$C$3:$C$21,Sheet2!$C$3:$C$21)),--(ISNUMBER(SEARCH($A3,Sheet2!$C$3:$C$21))))

Where this:

--(1/COUNTIF(Sheet2!$C$3:$C$21,Sheet2!$C$3:$C$21))

counts the no. of unique entries in C

and this:

--(ISNUMBER(SEARCH($A3,Sheet2!$C$3:$C$21)))

calculates the number of entries in C on Sheet2 that contain the criteria value...

thus multiplying the 2 together gives you your unique count of entries where the entries contain your criteria value.

Thanks for all your help everyone, even if I didn't end up using a helper cell or pivot table, I appreciate your time. These ways seemed a bit more difficult than an equation.

RichardSchollar - Your formula does exactly what I wanted to do, thanks a lot for your help.

Cheers