please help about count value

[imran ashraf]

[FONT=&quot]i need to count numerical values greater than 3 and less than 3 values from duplicate multi criteria.[/FONT]
[FONT=&quot]i have column A which have locations and Column B have numerical value . just need to count numerical value in front of on first available location others duplicate location should be zero.[/FONT]

[FONT=&quot]A B C less than 3 D greater than 3[/FONT]
[FONT=&quot]Italy 9 1 2[/FONT]
[FONT=&quot]Italy 2 0 0[/FONT]
[FONT=&quot]France 7 1 1[/FONT]
[FONT=&quot]Italy 4 0 0[/FONT]
[FONT=&quot]France 2 0 0[/FONT]

 

[admiral100]

Hi,

try this with Control+Shift+Enter NOT just Enter and drag down

C2 =IFERROR(INDEX($A$2:$A$6, MATCH(0,IF(LEN($B$2:$B$6)<=3,COUNTIF($C$1:C1, $A$2:$A$6),1),0)),"")
D2 =IFERROR(INDEX($A$2:$A$6, MATCH(0,IF(LEN($B$2:$B$6)>3,COUNTIF($D$1:D1, $A$2:$A$6),1),0)),"")


[TABLE="width: 317"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: left"]less than 3[/TD]
[TD="align: left"] greater than 3[/TD]
[/TR]
[TR]
[TD="align: left"]italy[/TD]
[TD="align: right"]912[/TD]
[TD="align: left"]italy[/TD]
[TD="align: left"]france[/TD]
[/TR]
[TR]
[TD="align: left"]italy[/TD]
[TD="align: right"]200[/TD]
[TD="align: left"]france[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]france[/TD]
[TD="align: right"]711[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]italy[/TD]
[TD="align: right"]400[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]france[/TD]
[TD="align: right"]2345[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[imran ashraf]

Hi,
please i need to be count numerical values less than 3 and greater than 3 on base of locations. if find duplicate location in the rang that should be equal to zero or empty. Example . Italy have 3 numerical value (9,2,4) hare is one less than 3 and 2 more than 3 and other duplicate Italy is zero.


[TABLE="width: 317"]
<tbody>[TR]
[TD]A[/TD]
[TD] B [/TD]
[TD="align: left"]count less than 3 numerical value[/TD]
[TD="align: left"]count greater than 3 numerical value[/TD]
[/TR]
[TR]
[TD="align: left"]italy[/TD]
[TD="align: right"]9[/TD]
[TD="align: left"] 1[/TD]
[TD="align: left"] 2[/TD]
[/TR]
[TR]
[TD="align: left"]italy[/TD]
[TD="align: right"]2[/TD]
[TD="align: left"] 0[/TD]
[TD] 0[/TD]
[/TR]
[TR]
[TD="align: left"]france[/TD]
[TD="align: right"]7[/TD]
[TD] 1[/TD]
[TD] 1[/TD]
[/TR]
[TR]
[TD="align: left"]italy[/TD]
[TD="align: right"]4[/TD]
[TD] 0[/TD]
[TD] 0[/TD]
[/TR]
[TR]
[TD="align: left"]france[/TD]
[TD="align: right"]2[/TD]
[TD] 0[/TD]
[TD] 0[/TD]
[/TR]
</tbody>[/TABLE]

 

[admiral100]

Now it's more clear )

just enter and drag down
D2 =IF(COUNTIF($A$2:A2,A2)>1,0,SUMPRODUCT(--($B$2:$B$6>3),--($A$2:$A$6=A2)))
C2 =IF(COUNTIF($A$2:A2,A2)>1,0,SUMPRODUCT(--($B$2:$B$6<3),--($A$2:$A$6=A2)))

 

[imran ashraf]

Thanks so much ! for helping its working but its count empty cells also in category less than 3 . please could you amend in formula that should not count empty cell . Many Thanks

 

[admiral100]

=if(countif($a$2:a2,a2)>1,0,sumproduct(--($b$2:$b$6<3),--($b$2:$b$6<>0),--($a$2:$a$6=a2)))

 

[imran ashraf]

Many! Many! Many! Thanks Admiral100..you provide me what i need .