Excel Conditional Running Total

Assuming you have the backorders in Column C, here is the formula you can use in Cell D2 '=COUNTIFS(C:C,"=1")'

The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met. This formula can be used with criteria based on dates, numbers, text, and other conditions.

I think that will overstate the result as some orders have more than 1 backorder line. The order should only be counted once

this is my closest attempt by making a running total by Order#
IF(A2=A1,C1+C2,C1)

I was then hoping to simply count the 1's,
but subsequent 0 lines for that order report as 1 messing the count.

I see what you mean... the 1 should be counted only one per order number.

I think the answer is with a nested IF formula, just struggling with the logic

I think I got it..... in cell D2
IF(A2<>A1,C1,IF(C1=1,D2,0))

Then use a countIF to count the 1's

You can do this with a pivot table. See attached.

PS, with the sample list, your result should be 4:
Ord 100, lines 2 & 4
Ord 102, line 1
Ord 103, line 2
Ord 105, lines 1 & 3
Order-Pivot.xlsx

Assuming you have Order No in Column A starting from second row.
Try below in B2 and drag down

=IF(A2=A2,COUNTIF($B$2:B2,A2),"")

Alternative in D2:

=IF(A2=A1,0,IF(COUNTIFS($A$2:$A$14,$A2,$C$2:$C$14,1)>0,1,0))

Copy down as required after adjusting the ranges for A2:A14 and C2:C14 or change to whole column references:

=IF(A2=A1,0,IF(COUNTIFS($A:$A,$A2,$C:$C,1)>0,1,0))

Or add this User Defined Function

Function Backordered(r As Range) As Long
Dim lngRow As Long
Dim colBackordered As New Collection

For lngRow = 2 To Range(r.Address).Row + Range(r.Address).Rows.Count - 1
    If Cells(lngRow, "C") <> "0" Then
        On Error Resume Next
        colBackordered.Add Cells(lngRow, "A"), CStr(Cells(lngRow, "A"))
        On Error GoTo 0
    End If
Next
Backordered = colBackordered.Count
End Function

Select allOpen in new window

Usage: =Backordered(A2:A14)

Just in case you need them, here are instructions on how to install and use the UDF.

In Excel, Press Alt+F11 to open the Visual Basic Editor (VBE)

Right-click on your workbook name in the "Project-VBAProject" pane. If you don’t see an existing module then select Insert -> Module from the menu bar. Otherwise just select the module.

Copy the UDF (you can use the ‘Select All’ button if you like) and paste it into the right-hand pane of the VBA editor ("Module1" window)

Press Alt+F11 again to go back to Excel

When you close the workbook you will need to save it as an xlsm or xls file if it’s not already one of those.

Slightly shorter option:

=IF(A2=A1,0,MIN(COUNTIFS($A:$A,$A2,$C:$C,1),1))

This puts zero when the order numbers match or gives lower of 1 or count of order with back-order lines.

I agree on the Pivot Table solution, but did accomplish a little differently.

See attached.

The Pivot table in my example counts the number of line items on each order and number of backorder items.
The CountIfs function outside of the Pivot Table counts the number of orders with backorders.
Data-for-example.xlsx