Unique values with multiple conditions

[vddavid]

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

 

[jimboy]

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

 

[Dave Patton]

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

 

[Dave Patton]

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.