Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 out of 5?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 out of 5?
One possibility:
Start in D1 and copy down. =IF(COUNTIF(INDIRECT("A1:A" & ROW()),A1)2,A1,"") HTH, Paul -- "mpenkala" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 out of 5?
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 "PCLIVE" wrote: One possibility: Start in D1 and copy down. =IF(COUNTIF(INDIRECT("A1:A" & ROW()),A1)2,A1,"") HTH, Paul -- "mpenkala" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 out of 5?
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 "mpenkala" wrote: 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 "PCLIVE" wrote: One possibility: Start in D1 and copy down. =IF(COUNTIF(INDIRECT("A1:A" & ROW()),A1)2,A1,"") HTH, Paul -- "mpenkala" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 out of 5?
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 -- "mpenkala" wrote in message ... 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 "PCLIVE" wrote: One possibility: Start in D1 and copy down. =IF(COUNTIF(INDIRECT("A1:A" & ROW()),A1)2,A1,"") HTH, Paul -- "mpenkala" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 out of 5?
Glad you got it working. Sometimes we tend to overthink things and that is
exactly what I did. Regards, Paul -- "mpenkala" wrote in message ... 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 "mpenkala" wrote: 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 "PCLIVE" wrote: One possibility: Start in D1 and copy down. =IF(COUNTIF(INDIRECT("A1:A" & ROW()),A1)2,A1,"") HTH, Paul -- "mpenkala" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|