Count rows that meet 2 IF conditions

[sneeland]

I know this shouldn't be fighting me...

Trying to put together o365 VB macro. I have some data. Need to walk each row. Need a count of total rows for each "PRIORITY" (column name) value of 1 thru 4 and the count of each that "Percent Complete" IS NOT "100.00". Want to write these counts on a newly created sheet in the same workbook.

Source data:

Desired output:

I've spent too much time on this and I know I'm missing one small thing. I'd appreciate any solutions from the group. Thank you so much!!!

Steve

 

[Yongle]

WELCOME TO MREXCEL

Try this
Amend first condition if required (see note below)
This is the Excel formula which must work
=COUNTIFS(I:I,"<1",G:G,"1*")

Run from the sheet containing the values to be counted

Sub CountRows()
    Dim Priority As Range, Percent As Range, p As Long, ws As Worksheet, pCount As Long
    With ActiveSheet
        Set Priority = .Range("G2", .Range("G" & .Rows.Count).End(xlUp))
    End With
    Set Percent = Priority.Offset(, 2)
    Set ws = Sheets.Add
    ws.Cells(1, 2) = "Open"
    For p = 1 To 4
        pCount = WorksheetFunction.CountIfs(Percent, "<1", Priority, p & "*")
        ws.Cells(p + 1, 1).Resize(, 2) = Array("Priority " & p, pCount)
    Next p
End Sub

NOTE
Condition may be incorrect - it depends on the format of the underlying data in percent column

May need to be
Percent, "<100"
or if text
Percent, "<>100.00"

If you cannot get it to work, let me know

 

[Yongle]

You could also use a formula and avoid VBA

Book1
	A	B
1		Open
2	Priority 1	2
3	Priority 2	3
4	Priority 3	4
5	Priority 4	3
Sheet6

Cell Formulas
Range	Formula
A2:A5	A2	="Priority " & ROW(A2)-1
B2:B5	B2	=COUNTIFS(Sheet1!I:I,"<1",Sheet1!G:G,ROW(A2)-1&"*")

 

[sneeland]

You are amazing! I did have to make the suggested change since the cell with "100.00" is being considered as text. Works perfectly! And only 30 lines less than my failed attempt I do need to keep it as a script to append to some other subroutines I have running also.

Sub CountRows()
Dim Priority As Range, Percent As Range, p As Long, ws As Worksheet, pCount As Long
With ActiveSheet
Set Priority = .Range("G2", .Range("G" & .Rows.Count).End(xlUp))
End With
Set Percent = Priority.Offset(, 2)
Set ws = Sheets.Add
ws.Cells(1, 2) = "Open"
For p = 1 To 4
pCount = WorksheetFunction.CountIfs(Percent, "<>100.00", Priority, p & "*")
ws.Cells(p + 1, 1).Resize(, 2) = Array("Priority " & p, pCount)
Next p
End Sub


Thank you so much!!

Steve