Thanks:  0
Likes:  0

# Thread: Unique values with multiple conditions

1. Hello everybody ,

I find myself confronted to a wierd problem ;

I wish to count in a list all the unique values that meet four different
criterias .
So far I managed to workout the folowing formula :
{Sum(if(\$C\$53:C\$4983>=\$F\$4)*(\$C\$53:\$C\$4983<=\$J\$4)*(\$I\$53:\$I\$4983=\$D\$4)*(\$B\$53:\$B\$4983=E5);1/countif(F53:F4983;F53:F4983))}
but it doesn't work

Any ideas

thanks

david

2. How about doing countif in 4 different cells then adding them up?

3. Consider the following; array enter it on a test range or edit for your information.

=SUM(IF((C4:C7>=C1)*(C4:C7<=C2)*(I4:I7=D1)*(B4:B7=B1),1,0))

This will count how many meet the 4 criteria.
Extend to determine how many are unique.

[ This Message was edited by: Dave Patton on 2002-04-12 07:22 ]

4. 2 ideas

1. Determine by row if the data meets the criteria

=IF((C4>=\$C\$1)*(C4<=\$C\$2)*(I4=\$D\$1)*(B4=\$B\$1)=1,C4,"")

This puts the amount in say Column G

Then array enter the following to determine how many are unique

=SUM(IF(LEN(G4:G7),1/COUNTIF(G4:G7,G4:G7)))

2. use an array formula (I used named ranges but that is not essential)

=SUM((MATCH(rC_,rC_,0)=(ROW(rC_)-MIN(ROW(rC_))+1))*(rB_=B1)*(rI_=D1))

Revise the references as necessary.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•