CODEWITHSUNDEEP
vbaexcelcountif
stack mark
stack mark

Reputation: 101

CountIf formula mismatch

Can you help me with a COUNTIF formula?

I have these conditions - as if I were applying a filter:

I'm using the below formula but getting the wrong count:

With ActiveWorkbook.Worksheets("TT")
[AH124] = wf.CountIfs(.Range("K:K"), "Tablet", .Range("K:K"), "NA") + wf.CountIf(.Range("I:I"), "<>Duplicate TT") + wf.CountIf(.Range("G:G"), "Yes")

End With

Upvotes: 1

Views: 213

Answers (3)

Jeremy
Jeremy

Reputation: 1337

Your Range("I:I"), "<>Duplicate TT" is going to pick all the blanks all the way to the bottom of the sheet so you should restrict the range to the bottom row: Range("I2:I & Range("I" & Cells.Rows.Count).End(xlUp).Row), "<>Duplicate TT" (this assumes you're stating to the check on the second row because of headers) and you should probably do that for the rest of your formula too

Upvotes: 0

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

Your first clause is the issue because no value in column K will be both Tablet and NA at the same time:

wf.CountIfs(.Range("K:K"), "Tablet", .Range("K:K"), "NA")

So you need to re-arrange the formula:

With ActiveWorkbook.Worksheets("TT")
    [AH124] = wf.CountIfs(.Range("K:K"), "Tablet", .Range("I:I"), "<>Duplicate TT", .Range("G:G"), "Yes") +  _
        wf.CountIfs(.Range("K:K"), "NA", .Range("I:I"), "<>Duplicate TT", .Range("G:G"), "Yes")

End With

Upvotes: 2

R3uK
R3uK

Reputation: 14537

Try this :

With ActiveWorkbook.Worksheets("TT")
    [AH124] = wf.CountIfs(.Range("K:K"), "=Tablet", .Range("K:K"), "=NA") + _
                wf.CountIf(.Range("I:I"), "<>Duplicate TT") + _
                wf.CountIf(.Range("G:G"), "=Yes")
End With

I've added = in your tests, because without it it'll count all cells containing Tablet, NA, Yes, ...

Upvotes: 0

Related Questions