count the number of times a particular situation has value

[vinod_siwan123]

Example
CITY VALUE
FARIDABAD 10
SONIPAT
FARIDABAD
GURGAON
FARIDABAD 2
YAMUNANAGAR 1


We can count that total number of value is 3 but if i would like to count faridabad has how many count in this example it is 2 number .

So please provide a formula to count as we have for sum sumif to sum but to count a non blank condition count if not give the correct result.

Thanks in advance for your efforts to solve my problem.

 

[Somendra Misra]

Hi Vinod,

See the file.

Regards,

 

[Swapnil]

Was jus

Hi Vinod,

See the file.

Regards,

@ somendra

We may miss out blank cells and might end up counting the same , Just a bit modification to formula can take care of such omissions.

=COUNTIFS($A$2:$A$7,A10,$B$2:$B$7,"<>0",$B$2:$B$7,"<>")

 

[Somendra Misra]

@Swapnil

This may also fetch you the result.

=SUMPRODUCT(($A$2:$A$7=A10)*ISNUMBER($B$2:$B$7)*($B$2:$B$7>0))

Regards,

 

[vinod_siwan123]

@Swapnil

This may also fetch you the result.

=SUMPRODUCT(($A$2:$A$7=A10)*ISNUMBER($B$2:$B$7)*($B$2:$B$7>0))

Regards,

Thanks Swapnil for your reply.

 

[vinod_siwan123]

Thanks

Was jus


@ somendra

We may miss out blank cells and might end up counting the same , Just a bit modification to formula can take care of such omissions.

=COUNTIFS($A$2:$A$7,A10,$B$2:$B$7,"<>0",$B$2:$B$7,"<>")

Thanks Mr. Somendra

 

[Swapnil]

Thank Somendra , He gave you the better solution to problem

 

[Somendra Misra]

@vinod_siwan123

Thanks for the feedback and welcome back.

Regards,