formula looking up multiple information.

T

Tamara

Excel 2003. I'm wanting a formula that encompasses all 3 requirements:
1. If a number in column A appears multiple times in column B.
2. Then if 2 different pre-determinded names appear in column C (there's
one name per cell) that are in the same rows as the numbers found in column
B.
3. Bring back the count of times the number appears in column B, less the
number of times the 2 names appear in column C (if only 1 name appears, just
the total count of numbers found in column B)
 
T

Tamara

Here's an example of what I'm trying to do:

ColA ColB ColC ColD
L1 15 15 tin
L2 15 plea
L3 15 tin
L4 19 19 tin
L5 19 19 serial
L6 22 22 tin

I would like to know how many items in column c there are for each number in
column A. If however tin and plea appear in the same group, they would only
count as 1.
 
B

Biff

Hi!
I would like to know how many items in column c there are for each number
in
column A. If however tin and plea appear in the same group, they would
only
count as 1.
Click to expand...

In other words, you want to count the unique values for each group.

You need to have a "group" number associated with each entry in column C so
you'd base this formula on the "groups" in column B, not column A.

List the unique group numbers:

A10 = 15
A11 = 19
A12 = 22

Enter this formula in B10 as an array using the key combo of
CTRL,SHIFT,ENTER:

=SUM(N(FREQUENCY(IF(B$2:B$7=A10,MATCH(C$2:C$7,C$2:C$7,0)),MATCH(C$2:C$7,C$2:C$7,0))>0))

Copy down to B12

Biff
 
T

Tamara

Hi,

Thanks for that ...
With the formula though, I'm trying to make it read so if only the words
"tin" and "plea" are in the same group they're to be counted as one. If they
appear in a group with a different text name they are to be counted
individually. ie
If the product group had:
tin, tin, serial this would count as 3
tin, plea, serial this would count as 2 (as tin and plea are in the same
group)
plea, plea count as 2 and so on .....

Is there a formula for this?
 
Top