3 out of 5?

[mpenkala]

Hi there,

I'm looking to do the following:
In column B I have either Q1,Q2,Q3 or Q4. What I'm looking for is for Cell
D5 to check cells B1:B5 and if we have 3 of the same Q to show me that Q.
example:
ColB ColD
Q1
Q1
Q2
Q4
Q1 Q1 <----- since Q1 has been given 3 in the past five, I need it
to show that

Any ideas? I could use multiple columns and do IF,AND,OR loops for each
individually, but there's gotta be an easier way.

Thanks,
Matt

 

[PCLIVE]

One possibility:

Start in D1 and copy down.
=IF(COUNTIF(INDIRECT("A1:A" & ROW()),A1)>2,A1,"")

HTH,
Paul

 

[mpenkala]

Hey PCLIVE,
thanks for this. It works except for one problem.
The A1 in "A1:A" stays at 1, were it should change each row
(A2,A3,A4...etc). Anyway to fix this without changing each manually?

Thanks,
matt

 

[mpenkala]

Nevermind PCLIVE, I found a fix:

=IF(COUNTIF(B5:B9,B9)>2,B9,"")

Just got rid of the INDIRECT and added my own range.

Thanks again!
Matt

 

[PCLIVE]

Sorry I misread,

I'm assuming that it should be column B. In that case it should be:

=IF(COUNTIF(INDIRECT("B1:B" & ROW()),B1)>2,B1,"")

Now to address your question, maybe. The formula counts the number of times
the corresponding value in column B occurs in the range from the top to the
current row. Is that not what you want? Or do you only want the formula to
search from the current row through the past 5 rows including the current
row? If that is the case, then:

=IF(ROW()-4<1,"",IF(COUNTIF(INDIRECT("B"&ROW()-4&":B"&ROW()),B1)>2,B1,""))

Does that help?
Paul



--

 

[PCLIVE]

Glad you got it working. Sometimes we tend to overthink things and that is
exactly what I did.

Regards,
Paul

--