software

Excel file (sample) is shown below
A B C D E
1 a p 1 aq
2 a q 1 sd
3 b p 2 we
4 c q 2 xs
5 a r 2 as
6 d p 1 aq
7 b q 1 sd
8 a q 2 we

There is another sheet 2/ file which has the list of items from column C i.e.
X Y
p
q
r
etc.

What i require is a formula in column Y in sheet 2, which would count no. of
unique items from column B of sheet 1 where the column C in Sheet 1 is equal
to value in Column X in Sheet 2.
that is whatever values be in Column B of Sheet 1, i want just their total
number of unique items for every value in Column c which is equal to values
in Colmn X of Sheet 2
i.e. for every quot;pquot; in Sheet 1 in column C i would like the sum of unique
count of items (a,b,c) which are there in column B of sheet 1 - if there are
a, a, b, c, etc. then i want sum of unique 3 and not 4 as the result of my
formula
Can anyone please suggest a formula for the same.

To use standard worksheet formulas, you would need to use a helper column of formulas for each item
that you want to identify unique values for. For example, a column of formulas like

=IF(Sheet1!C2=quot;pquot;,Sheet1!B2,quot;quot;)

copied down to match the length of your data set. Then you can count the unique items with

=SUM(1/COUNTIF(B2:BXX,B2:BXX))-1

(assuming that the first formulas are in column B of sheet2, rows 2 through XX). This formula must
be array entered (entered using Ctrl-Shift-Enter, not just enter).

If you don't have an aversion to VBA, you could easily use a User-Defined-Function, without the need
for the helper columns.

HTH,
Bernie
MS Excel MVPquot;d_h_sanjayquot; lt;u20024@uwegt; wrote in message news:5db0931b3e715@uwe...
gt; Excel file (sample) is shown below
gt; A B C D E
gt; 1 a p 1 aq
gt; 2 a q 1 sd
gt; 3 b p 2 we
gt; 4 c q 2 xs
gt; 5 a r 2 as
gt; 6 d p 1 aq
gt; 7 b q 1 sd
gt; 8 a q 2 we
gt;
gt; There is another sheet 2/ file which has the list of items from column C i.e.
gt; X Y
gt; p
gt; q
gt; r
gt; etc.
gt;
gt; What i require is a formula in column Y in sheet 2, which would count no. of
gt; unique items from column B of sheet 1 where the column C in Sheet 1 is equal
gt; to value in Column X in Sheet 2.
gt; that is whatever values be in Column B of Sheet 1, i want just their total
gt; number of unique items for every value in Column c which is equal to values
gt; in Colmn X of Sheet 2
gt; i.e. for every quot;pquot; in Sheet 1 in column C i would like the sum of unique
gt; count of items (a,b,c) which are there in column B of sheet 1 - if there are
gt; a, a, b, c, etc. then i want sum of unique 3 and not 4 as the result of my
gt; formula
gt; Can anyone please suggest a formula for the same.