Pass column values to a single cell

[rickblunt]

Greetings, I am using the following formula to count how many rows in a particular column have not been completed. Column B is the task, Column H indicates if it is completed or not - pretty straight forward. It occurs to me that it would be nice to create a list of what these remaining items actually were as there are currently over 300+. So lets say in cell Z1 I would get a list of all of the remaining tasks, (and then i could even pass this cells value to a userform, that would be cool ) Anyway, I am stumped on a formula to do this, usually I can figure out something that at least partially works. Am I completely wrong on this and should I use code instead?

I appreciate any input - thanks,

=COUNTIFS('Device List'!B:B,"Outdoor Area",'Device List'!H:H,"")

 

[Sufiyan97]

What does the criteria 2 "" mean do you want to count blank rows?

=COUNTIFS('Device List'!B:B,"Outdoor Area",'Device List'!H:H,"")

 

[rickblunt]

correct, for example if B57 has a value in it, but H57 doesn't have a value in (the cell is blank) then I want the value from B57 to end up in Z1. The thing is that initially there would be 300+ values in this cell as we begin our inspections.

It occurred to me after I sent out my initial question that I am basically just applying a filter to this set of data. If I could figure out how to pass the results to that cell then this would accomplish my task. The code that I am using for the filter is the following, if I could pass those results to a cell that would be great. Eventually I will kick it over to a textbox in a userform for the finished project.

Sub FireExinguisherList()

' This creates a list of the remaining fire extinguishers that need to be inspected.
    
    Range("I1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$H$275").AutoFilter Field:=1, Criteria1:= _
        "Fire Extinguisher"
    ActiveSheet.Range("$A$1:$H$275").AutoFilter Field:=8, Criteria1:="="
End Sub

 

[rickblunt]

I actually got this figured out, once I realized that I was probably going to use a userform anyway it made it a lot easier. In case anyone ever runs across this and needs a solution....

This will sort out a worksheet by two parameters and then return two columns of data into one textbox on a userform.

Private Sub Userform_Initialize()
    Dim i As Long
    Dim Lastrow As Long
      
    ListBox1.SetFocus
    
 'this is from the DEVICE ID column
    Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
         For i = 1 To Lastrow
             If Cells(i, 8).Value = "" And Cells(i, 2).Value = "Warehouse Area" Then ListBox1.AddItem Cells(i, 3).Value & " - " + Cells(i, 4).Value
         Next
  
End Sub