Counting same values in consecutive columns

**santhoosan** · 12-11-2013, 03:23 AM

Hi,

I was trying to see the values only if the same value arrived more than three times in the given range.

Ex:

A B C D E F G H I J
1 A A A A B A A
2

in the given range A1:J1, I would like to count 'A' only if it repeats more than or equal to 3 times. For row 1 I should get count as 4.

Can someone please help me fixing this issue.

Regards,
Santhosh

**benishiryo** · 12-11-2013, 03:48 AM

welcome to the forum, Santhosh. maybe an array formula like this:

Formula:

=IF(MAX(FREQUENCY(IF(A1:J1="A",COLUMN(A1:J1)),IF(A1:J1<>"A",COLUMN(A1:J1))))>=3,MAX(FREQUENCY(IF(A1:J1="A",COLUMN(A1:J1)),IF(A1:J1<>"A",COLUMN(A1:J1)))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.