Count IF formula?

[bflan0524]

hello
I don't know if this would be a count if formula, or something else, any ideas
I have 3 columns of data that have unique ID Codes
for example
Column A Row 1: ABC
Column B Row 1: BCD
Column C Row 1: ABC

I want to show the % of time that one officer code was the same, so for this example ABC was the officer code 67% of the time
thank you

 

[Caleeco]

You could put the following formula into cell D1, and format the cell type to be Percentage:

=COUNTIF($A1:$C1,"ABC")/3

Do you wish to check the percentage of "ABC" for every row?

Caleeco

 

[bflan0524]

thank you, but each row could be totally different set of unique codes. so for example

Column A row 1: ABC Column B Row 1: BCD Column C Row 1: ABC
Column A Row 2: CDE Column B Row 2: CDE Column C Row 2: BCF

 

[Caleeco]

Hello,

OK, maybe i misunderstood. Do you want to check which CODE appears the most number of times on each row?
What happens when there is a draw? eg ABC, BCD, CDE?

Caleeco

 

[bflan0524]

correct, sorry I was probably unclear with my description
knowing the data set that shouldn't happen

 

[Caleeco]

Ok no problem. And what do you want to return? Just the % in column D? or the most frequent code in column D, with % of that code in Column E?

Thanks
Caleeco

 

[bflan0524]

I think the latter would be best, the most frequent code in D and the % In column E
actually as I did look at the data set though I did see an example of a draw

could I add another column too # of times changed, so in the example of where it was a draw eg ABC, BCD, CDE it would be show 3
if it was ABC, ABC, ABC it would be 0
etc

does that make sense? thank you for your patience

 

[bflan0524]

also in the example above for how many times it changed, if it went from blank to say ABC that would obviously count as a change

ty

 

[Caleeco]

Hello, try this for the first part of your question:

In Column D1 use this equation to return the most frequent code:
<countif($a1:$c1,$b1),if(countif($a1:$c1,$a1)<countif($a1:$c1,$c1),3,2),1))[ code]
<countif($a1:$c1,$b1),if(countif($a1:$c1,$a1)<countif($a1:$c1,$c1),3,2),1))
<countif($a1:$c1,$b1),if(countif($a1:$c1,$a1)<countif($a1:$c1,$c1),3,2),1))
EDIT: Formula not posting correctly

<countif($a1:$c1,$b1),if(countif($a1:$c1,$a1)<countif($a1:$c1,$c1),3,2),1))[ code]
In column E1 use this equation to return the %:

=COUNTIF($A1:$C1,D1)/3

Copy rows down as needed. Does this return the expected results?

Caleeco</countif($a1:$c1,$b1),if(countif($a1:$c1,$a1)<countif($a1:$c1,$c1),3,2),1))[></countif($a1:$c1,$b1),if(countif($a1:$c1,$a1)<countif($a1:$c1,$c1),3,2),1))
</countif($a1:$c1,$b1),if(countif($a1:$c1,$a1)<countif($a1:$c1,$c1),3,2),1))
</countif($a1:$c1,$b1),if(countif($a1:$c1,$a1)<countif($a1:$c1,$c1),3,2),1))[>

 

[bflan0524]

for the first one it says I am missing a (....I put one in the end but it still didn't work