"If statements"

[173]

I need help with "if statements". Sheet one will contain the following:

DATE A B C D E
2/22/2008 TRUE FALSE FALSE FALSE Ron
2/23/2008 FALSE TRUE TRUE TRUE Phill
2/24/2008 FALSE TRUE FALSE FALSE Tracy
2/25/2008 FALSE FALSE FALSE FALSE Sharon
2/26/2008 TRUE TRUE FALSE TRUE Bill

On sheet two I need to list any date that has three or more true statements with the coresponding name.
EXAMPLE:
2/23/2008 Phill
2/26/2008 Bill

[rylo]

Hi

Here's one way using functions.

1) Sheet1!G2: =COUNTIF(B2:E2,TRUE)
2) Copy from G2 down to G6
3) Sheet2!A1: =IF(ROW()>COUNTIF(Sheet1!$G$2:$G$6,">2"),"",INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$G$2:$G$6>2,ROW(Sheet1!$G$2:$G$6),""),ROW())))
This formula is array entered. Hold down the shift and ctrl keys, then press enter.
4) Sheet2!B1: =IF(A1<>"",INDEX(Sheet1!F:F,SMALL(IF(Sheet1!$G$2:$G$6>2,ROW(Sheet1!$G$2:$G$6),""),ROW())),"")
This formula is also array entered.
5) Copy from A1:B1 down to A6.

There are other ways of doing this that use code. If you are happy with a macro response, or even a User Defined Function, then post back and I'm sure these could be arranged.

rylo

[earthtodan]

I have an idea that may be simpler, if also slightly intermediate.

In sheet 1 cell F2, type =countif(B2:E2,TRUE)
Drag down to include your range, hide column F.

In sheet 2, in the first cell you want to display a date, type =if(sheet1!F2=>3,sheet1!A2,"")
In the next colum over, type =if(sheet1!F2=>3,sheet1!E2,"")

Drag these cells down to include as much range as you need. Then highlight one column and apply a filter. Keep it filtered for non-blanks.

Dan

P.S. Put the filter on its own row, then hide that row. The new entries should just stack up below each other.

[173]

Thanks guys

[173]

Thanks
SOLVED