count unique values with criteria

  • Thread starter Thread starter ken smith
  • Start date Start date
K

ken smith

XL2K on W2K - In the list below I need to determine the
number of unique, non-blank values in Grp for each Team.
Returns needed: Team 1 - 2, Team 2 - 1. Then I need to add
the number of blanks per team. Final answers: Team 1 - 4,
Team 2 - 2. Preferably in one formula (array I assume).
Any ideas?

Acct Grp Team
a xx 1
b xx 1
c 1
d yy 1
e yy 1
f yy 1
g 1
h zz 2
i zz 2
j 2

TIA,
Ken Smith
 
May well be an easier way but I'm feeling rusty and braindead at the moment, so
here goes my 2p worth anyway:-

Assuming your data currently sits in say A1:C100, then in D1 put a heading of
'Uniques', and then in D2 put the following formula and copy down to D100

=IF(B2="",1,(1/SUMPRODUCT(($B$2:$B$11=B2)*($C$2:$C$11=C2))))

Select A1:D100 and do data / Pivot table and Pivot chart report / then hit Next
/ Next / Finish

Drag Grp to the Row fields area, Team to the Column fields area and then Count
to the Data area. make sure it is set to SUM (Should say Sum Uniques), but if
it doesn't then right click anywhere in the data area, select Field Settings and
choose Sum from the menu on the left.

Now, on the drop down under Grp deselect blanks and you will have your first set
of data in the table at the bottom. Use the dropdown and reselect your data and
you will have the second set you want.

This can also be accommodated using formulas and the SUMPRODUCT function on the
new table you now have in A1:D100, pulling in the data in Col D into the
equation.
 
I don't think there's an easier way if you're using a pivot table. I
used two columns in the source table. One counted unique groups,
including blanks:

=IF(OR(B2="",SUMPRODUCT(($B$2:$B2=B2)*($C$2:$C2=C2))>1),0,SUMPRODUCT(($B$2:$B2=B2)*($C$2:$C2=C2)))

The other counted unique groups, excluding blanks (array entered):


=IF(SUMPRODUCT(($B$2:$B2=B2)*($C$2:$C2=C2))>1,0,SUMPRODUCT(($B$2:$B2=B2)*($C$2:$C2=C2)))

In you example, each instance of blank seemed to count as a unique item
in the group. With my formula, it counted only once.
 
Hi Debra - That was how I read the Ops request. He wanted the number of
uniques, but then wanted a sum total of blanks per team added to that, hence the
effective count for the blanks but fractions to get me a sum total of my uniques
on the non-blanks. I am pretty well brain dead after the last few extremely
hectic weeks though, so may be just missing it, but it seemed to gel with the
expected answers though.
 
Thanks Ken. I'm pretty sure the original post said "unique blanks" the
first time I read it. It seems to have changed though. <g>
 
=IF(B2="",1,(1/SUMPRODUCT(($B$2:$B$11=B2)*($C$2:$C$11=C2))))


Dohhhhhhh - might have helped if I'd expanded the ranges to match the example
text :-(

=IF(B2="",1,(1/SUMPRODUCT(($B$2:$B$100=B2)*($C$2:$C$100=C2))))
 
Thank you all. I had forgotten about SUMPRODUCT. I ended up skipping the pivot table, naming the columns and using SUMIF: =SUMIF(Team,1,Uniques)
Thanks again, ken
 
You skipped my beloved Pivot tables!!! YOU DID WHAT??????????????? <g>

Glad you got sorted ;-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



ken smith said:
Thank you all. I had forgotten about SUMPRODUCT. I ended up skipping the pivot
Click to expand...
table, naming the columns and using SUMIF: =SUMIF(Team,1,Uniques).
 
Back
Top