PDA

View Full Version : CountIf and only different values



Sir Babydum GBE
01-16-2012, 10:08 AM
Hi

I have a purchase history log

Suppose in column A I have a list of 10 people and their names appear several times (1 row every for each product they order). So if Bob buys 10 oranges today, 5 apples tomorrow and 6 bananas next week. His name will appear 21 times on my sheet.

Peter might buy 3 bananas. So all in all - Peter's name is on the list 3 times. However, in column 2 is the list of fruit. So we have 10 oranges, 5 apples and 9 bananas

But want I want to do is return the following results next to each occurence of that persons name in column C:

Peter = 1
Bob = 3

Because Bob has bought 3 different types of fruit, Peter bought 1.

In actual fact its not fruit we're dealing with, its codes. So there may be thousands of customers and hundreds of different codes.

Any idea of what formula I need please?

BD

mdmackillop
01-16-2012, 11:42 AM
An Advanced Filter can give a table of Unique Values to which you can use a simple CountIf.

Sir Babydum GBE
01-16-2012, 02:46 PM
An Advanced Filter can give a table of Unique Values to which you can use a simple CountIf.Thanks MD. Actually I can't go this route because i need the results to be in the original table as they are used to help construct a pivot table.

But I've been digging aaround the web and there do seem to be some array formulas out there that look promising.

Cheers

BD

mdmackillop
01-16-2012, 03:15 PM
Is the columns A-C the result you are after?. If not, can you change it to suit.

Aflatoon
01-17-2012, 05:37 AM
Is this perhaps what you wanted?

redhunter
01-17-2012, 12:21 PM
If i got it right try :

Sub Only_Once()
Set rng = Range("A1:B" & Cells(65536, 1).End(xlUp).Row)
rng.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
rng.SpecialCells(xlCellTypeVisible).Select
Range("A1").Select
End Sub

Sir Babydum GBE
01-30-2012, 02:50 AM
Thanks everyone, I just came back of annual leave - I'll check these out presently

BD

shrivallabha
01-30-2012, 11:14 AM
And so will this formula variation based on Aflatoon's layout and data. Needs to be ARRAY Entered (CTRL + SHIFT + ENTER):

=SUM((FREQUENCY(MATCH($A$2:$A$23&$B$2:$B$23,$A$2:$A$23&$B$2:$B$23,0),MATCH($A$2:$A$23&$B$2:$B$23,$A$2:$A$23&$B$2:$B$23,0))>0)*ISNUMBER(SEARCH(A2,$A$2:$A$24,1)))