Count Filtered Column if not blank when adjacent column is blank

[Lyndonu]

In the spreadsheet below, i'm trying to create a formula to count when column O is not blank and column R is blank. (essentially counting what I've manually highlighted yellow.) The spreadsheet gets filtered so it cant be a simple countif.

Any assistance would be wonderful as I'm out of my league....

 

[JoeMo]

Welcome to the forum!
If you are willing to use a macro, here's one that will do the count. You can assign it to a button if desired.
To install standard module code:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the code from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Press Alt+F8 keys to run the code
7. Make sure you have enabled macros whenever you open the file or the code will not run.

Sub countSpecial()
Dim Col As Range, R As Range, V As Variant, i As Long, j As Long, Ct As Long
Set Col = Range("O:R")
On Error Resume Next
Set R = Col.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If R Is Nothing Then Exit Sub
For i = 1 To R.Areas.Count
    V = R.Areas(i)
    For j = 1 To UBound(V, 1)
        If V(j, 1) <> "" And V(j, 4) = "" Then Ct = Ct + 1
    Next j
    Erase V
Next i
MsgBox "Count is: " & Ct
End Sub

 

[Lyndonu]

Welcome to the forum!
If you are willing to use a macro, here's one that will do the count. You can assign it to a button if desired.
To install standard module code:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the code from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Press Alt+F8 keys to run the code
7. Make sure you have enabled macros whenever you open the file or the code will not run.

Sub countSpecial()
Dim Col As Range, R As Range, V As Variant, i As Long, j As Long, Ct As Long
Set Col = Range("O:R")
On Error Resume Next
Set R = Col.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If R Is Nothing Then Exit Sub
For i = 1 To R.Areas.Count
    V = R.Areas(i)
    For j = 1 To UBound(V, 1)
        If V(j, 1) <> "" And V(j, 4) = "" Then Ct = Ct + 1
    Next j
    Erase V
Next i
MsgBox "Count is: " & Ct
End Sub

Thankyou JoeMo.

Would it be possible for that count to be shown in a cell on the spreadsheet instead of as a message box?

 

[JoeMo]

Thankyou JoeMo.

Would it be possible for that count to be shown in a cell on the spreadsheet instead of as a message box?

You are welcome.

To show the count in a cell just replace the message box line with this:
Range("A1").Value = Ct
change the cell address (in red) to suit.