How do I write a COUNTIF function to count in one column and use criteria from multiple columns?

Question

I'm working on putting together a stock feed report, which I had asked about in a previous question. One of the fields takes the count of items that are currently being shipped, but it needs to utilize a criteria from a separate column pertaining to introductory dates (UDF_INTRODUCED).

I used a COUNTIF function to get the count of "UDF_INTRODUCED" within the last 6 months, and now need to get the count of items with a "FirstOfDuedate1" within that population. I don't know if this a nested COUNTIF, or if I would need to utilize COUNTIFS.

Image of the source data:

Source data headers and 7 lines

Image of current output, need to get a numerical value for field B7:

Current output

I'm attempting to use the COUNTIFS function to take the count based on multiple criteria as shown at the end of the code below:

Sub CommandButton1_Click()

Range("A2").Value = "Total FG"
Range("A3").Value = "Total out of stock"
Range("A4").Value = "Total out of stock intro past 6 months (included in 
Total out of stock)"
Range("A5").Value = "Total out of stock less past 6 months introductions"
Range("A6").Value = "Percentage in stock not included new introductions"
Range("A7").Value = "Items in Transit"
Range("A8").Value = "Percentage in stock including items in transit"

Dim MyPath As String, mps As Variant, mps_temp As String, mydate As Date, 
IntroDate As Date, i As Integer

MyPath = "C:\Users\Kirank\Documents\Stock Feed 
Analysis\HVL_Available_to_Sell_Report_with_Headers 2019.01.01"
mps = Split(MyPath, " ")

For i = LBound(mps) To UBound(mps)
    mps_temp = mps(UBound(mps) - i)
    If mps_temp Like "####.##.##" Then
        mydate = DateSerial(Mid(mps_temp, 1, 4), Mid(mps_temp, 6, 2), 
Mid(mps_temp, 9, 2))
        IntroDate = mydate - 181
        Exit For
    End If
Next
Range("B1").Value = mydate

    Range("B2").Select
    ActiveCell.FormulaR1C1 = 
"=COUNT(HVL_Available_to_Sell_Report_wi!C[3])"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = 
"=COUNTIF(HVL_Available_to_Sell_Report_wi!C[3],0)"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = 
"=COUNTIF(HVL_Available_to_Sell_Report_wi!C[13],"">=" & IntroDate & """)"
    Range("B5").Select
    ActiveCell.FormulaR1C1 = Range("B3") - Range("B4")
    Range("B6").Select
    ActiveCell.FormulaR1C1 = (Range("B2") - Range("B5")) / Range("B2")
    Selection.Style = "Percent"
    Range("B7").Select
    ActiveCell.FormulaR1C1 = 
"=COUNTIFS(HVL_Available_to_Sell_Report_wi!C[4]," > " & 0" & 
"HVL_Available_to_Sell_Report_wi!C[13],"">=" & IntroDate & """)"

End Sub

The output is resulting in "TRUE" rather than a number. The final value for this should be 45.

Not related to your question but you might find some use in this: How to avoid using Select in Excel VBA — erazorv4
@erazorv4 Thanks for that! I'm still new to VBA, so I'm just kind of following along by rote based on information that I'm finding on here and via Google-fu. Appreciate the feedback! — Kiran Kelkar

Greg Viers Greg Viers · Accepted Answer · 2019-06-06T18:13:39

The problem is in this code right here: > " & 0".

you want to have a >0 condition, but the way you have your quotation marks means that the > sign is outside of all of them, so you are basically doing a comparison operation. That's why you're getting True.

Try this:

ActiveCell.FormulaR1C1 = "=COUNTIFS(HVL_Available_to_Sell_Report_wi!C[4], "">0"",HVL_Available_to_Sell_Report_wi!C[13],"">=" & IntroDate & """)"

How do I write a COUNTIF function to count in one column and use criteria from multiple columns?

1 Answers