How to get the number of specific value of a cell based on the value of another cell

[resiexfor]

Hello all,

I need to get the number of all column F that is having 2 or more different values in column B?

for example...

MR2 is having one item in column b, so that counts as one, and i need to finde the count all other MRs that only have one value same as MR2

same apply to other MR1, which is having 2 values, and the same for it. need someway to get the count of all the MRs that have only 2 values.

Hope you got my point!

Is that possible to obtain from excel?

Thanks for your help

[AliGW]

Please manually add your expected results to your sample file to show us what you want.

[resiexfor]

This is what i need in the added column,
i've done it manually, but the list i have is over 6000 line.

Thanks

[Glenn Kennedy]

One way, using this array formula:

=SUM(IF(($F$2:$F$83=F2),1/COUNTIFS($B$2:$B$83,$B$2:$B$83,$F$2:$F$83,F2)))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Glenn Kennedy]

or a non-array alternative...

=SUM(INDEX(($F$2:$F$83=F2)/COUNTIFS($F$2:$F$83,$F$2:$F$83&"",$B$2:$B$83,$B$2:$B$83&""),0))