Show only items if "x"

collegeitdept said:

Does any on know of a formula that will only return the values in Column B only if it has an "X" in column A (without skipping a row).


Reference List

A B
x Item 1
Item 2
Item 3
Item 4
Item 5
x Item 6
x Item 7
x Item 8
Item 9
x Item 10



Returned values:

Item 1
Item 6
Item 7
Item 8
Item 10



Thanks.

Click to expand...

collegeitdept said:

Does any on know of a formula that will only return the values in Column B only if it has an "X" in column A (without skipping a row).

Click to expand...

Sub GetXedItems()
  Const FirstCellToOutputTo As String = "D1"
  Range(FirstCellToOutputTo).EntireColumn.Clear
  On Error Resume Next
  Columns("A").SpecialCells(xlCellTypeConstants).Offset(, 1).Copy Range(FirstCellToOutputTo)
End Sub

Steve=True said:

Hi Aladin,

Why doesn't the countif work inside the array? Seems like it should, but doesn't in testing.

Thanks

Steve=True

Click to expand...

collegeitdept said:

Those formulas worked great.

Here's how I applied it to my workbook:


{=IFERROR(IF(ROWS(B$3:B3)<="",INDEX('[- Universal Invoice Form.xls]Sheet1'!$D$2:$D$992,SMALL(IF('[- Universal Invoice Form.xls]Sheet1'!$L$2:$L$992="x",ROW('[- Universal Invoice Form.xls]Sheet1'!$D$2:$D$992)-ROW('[- Universal Invoice Form.xls]Sheet1'!$D$2)+1),ROWS(A$3:B3))),""),0)}


However, that is looks for one variable (if) - if cells in column L = x

How do I add another condition

So the formula returns only values in Column D with the conditions that cells in Column L = X AND Column M = 2?


Thanks.

Click to expand...