Count Unique Entires -- with conditions

[Erik Wynn]

Hi,

I am trying to do some "simple" counting with Excel using array functions.
Perhaps there is a much easier way to do this, but it's not jumping out at
me. I have looked at the postings, and see the first half of what I need,
using the FREQUENCY and MATCH functions, and this has got me started, but
I'm not sure how to do the next step...

I need to count the number of unique items in a range based on a condition
in a parallel range.

For example, to count the number of unique fruit items in the list:

FRUIT QTY CUSTOMER
-----------------------
apple 4 Tim
orange 4 Jennifer
apple 5 Mike
pear 3 Jennifer
pear 6 Veronica
banana 1 Flo
pear 2 Jennifer

Assume FRUIT_RANGE, QTY_RANGE, and CUSTOMER_RANGE are defined
appropriately.

So,
{=SUM(IF(FREQUENCY(MATCH(FRUIT_RANGE,FRUIT_RANGE,0),MATCH(FRUIT_RANGE,FRUIT_
RANGE,0))>0,1))} will yield 4 (count of {apple, orange, pear, banana}). But
now,

1. How to calculate the number of unique fruit items in the list when AMT

=4?

2. How to calculate the number of unique fruit items in the list ordered by
Jennifer?
3. How to calculate the number of unique fruit items in the list ordered by
Jennifer when AMT >= 4?

Thanks for your help!

Erik

 

[Jim]

How many did Jennifer order? =COUNTIF(C2:C100,"Jennifer")
How many apples did Jennifer order?
=SUMPRODUCT((A2:A100="apple")*(C2:C100="Jennifer")
How to calculate the number of unique fruit items in the list ordered by

Jennifer when AMT >= 4?

=SUMPRODUCT((C2:C100="Jennifer")*(B2:B100<=4))
How about the apple orders by Jennifer with the amount >=4?
=SUMPRODUCT((A2:A100="apple")*(C2:C100="Jennifer")*(B2:B100<=4))

 

[Peo Sjoblom]

Although I personally would use autofilter or advanced filter
or multiple formulas.

Having said that you could simplify the formula you have

=SUMPRODUCT((Fruit_Range<>"")/COUNTIF(Fruit_Range,Fruit_Range&""))

or with cell references

=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&""))

entered normally



Now for you questions, here are some formulas I once picked up from Harlan
Grove
I can't guarantee they will work 100% since these things are rather
complicated but they worked for all combinations I tested using your
example

Note that they all have to be array entered with ctrl + shift & enter

Assume the fruits are in A, the QTY in B and the customer in C

for 1.

=SUM(1/MMULT((IF((B2:B100=G1),A2:A100)=TRANSPOSE(IF((B2:B100=G1),A2:A100)))+
0,ROW(A2:A100)^0))-1

I replaced the criteria with a cell reference where I put the criteria
(G1), otherwise with 4 it would look like

=SUM(1/MMULT((IF((B2:B100=4),A2:A100)=TRANSPOSE(IF((B2:B100=4),A2:A100)))+0,
ROW(A2:A100)^0))-1

for 2.

=SUM(1/MMULT((IF((C2:C100=H1),A2:A100)=TRANSPOSE(IF((C2:C100=H1),A2:A100)))+
0,ROW(A2:A100)^0))-1

or hard coded

=SUM(1/MMULT((IF((C2:C100="Jennifer"),A2:A100)=TRANSPOSE(IF((C2:C100="Jennif
er"),A2:A100)))+0,ROW(A2:A100)^0))-1

finally for 3.


=SUM(1/MMULT((IF((B2:B100=G1)*(C2:C100=H1),A2:A100)=TRANSPOSE(IF((B2:B100=G1
)*(C2:C100=H1),A2:A100)))+0,ROW(A2:A100)^0))-1

or hard coded

=SUM(1/MMULT((IF((B2:B100=4)*(C2:C100="Jennifer"),A2:A100)=TRANSPOSE(IF((B2:
B100=4)*(C2:C100="Jennifer"),A2:A100)))+0,ROW(A2:A100)^0))-1

Note also that if you use 2 conditions for a customer where the criteria QTY
is not present it will return an erroneous number
so with customer Tim and QTY 4 it won't work, then you have to rely on
Customer Tim only

=SUM(1/MMULT((IF((C2:C100="Tim"),A2:A100)=TRANSPOSE(IF((C2:C100="Tim"),A2:A1
00)))+0,ROW(A2:A100)^0))-1

I still recommend that you use several formulas or filters instead

 

[Erik Wynn]

Hi Jim,

Thanks, this is getting close, but still not quite right. I need to count
only the unique items, not all items. For example:

How to calculate the number of unique fruit items in the list ordered by
Jennifer when AMT >= 4?

=SUMPRODUCT((C2:C100="Jennifer")*(B2:B100<=4))

Aside from the typo in the "<=", when changed to ">=", it does not quite do
the job. Jennifer has three orders in total, 2 of which are for fewer than
4 items, but these are both for pears. I need a formula that will count the
number of unique fruits (fruit items, plural) ordered, which is 1.

Databases are much easier !

Cheers,

Erik

 

[Erik Wynn]

Hi Peo,

Thanks, this is closer. Thanks for the simplification of the original
"unique" count using SUMPRODUCT() -- it's much easier to read.

As for the suggestions for tackling questions 1, 2, and 3, these might work
if the condition is a single number (4 in your example). I need to use "<="
and ">=" operators, and these formulae do not seem to work well with these
operators.

Perhaps I will take your advise and use the auto-filter to help simplify
life. There have got to be better things to do on a Saturday night!

Cheers,

Erik

 

[Peo Sjoblom]

Erik


This worked when I tested a simple table

=SUM(1/MMULT((IF((B2:B100>=F1)*(B2:B100<=G1)*(C2:C100=H1),A2:A100)=TRANSPOSE
(IF((B2:B100>=F1)*(B2:B100<=G1)*(C2:C100=H1),A2:A100)))+0,ROW(A2:A100)^0))-1

hard coded

=SUM(1/MMULT((IF((B2:B100>=2)*(B2:B100<=7)*(C2:C100="Jennifer"),A2:A100)=TRA
NSPOSE(IF((B2:B100>=2)*(B2:B100<=7)*(C2:C100="Jennifer"),A2:A100)))+0,ROW(A2
:A100)^0))-1

where it will return where the qty is between 2 and 7


However an easy way would be to concatenate 2 columns with conditions from
the middle columns, assume you want to check
for all Jennifer with qty between 2 and 7, in a help column put

=IF(AND(B2>=2,B2<=7,C2="Jennifer"),C2&A2,"")

or better

=IF(AND(B2>=$F$1,B2<=$G$1,C2=$H$1),C2&A2,"")

where the cells hold the criteria

copy down

now use the simplified unique count formula I posted on the help column


=SUMPRODUCT((E2:E100<>"")/COUNTIF(E2:E100,E2:E100&""))

assuming help column is column E

I can email you a sample workbook if you provide an email address

 

[Erik Wynn]

Thanks, Peo.

I don't think I will need the sample worksheet, but thanks anyways. I
appreciate your help on this. Given the choice, I certainly prefer using
SQL for data queries!

Cheers,

Erik