vba pass criteria to countif in variable

Question

I am trying to count the number of rows where a specific string appears in column K in a worksheet using a loop. The strings used for the search reside in Scorecards B2:B8. The count should be placed in the next column, C in this case. I am trying to pass the string in "op_id" to countif. When I run it all counted values in column C are 0. Here is what I have so far:

Dim op_id As String For i = 2 To 8 op_id = Sheets("Scorecards").Cells(i, 2) Sheets("Scorecards").Cells(i, 3) = "=COUNTIF('Raw Data'!K:K, & op_id)" Next i

Thanks in advance!

Can you specify which version of Excel you are using?

bonCodigo
– bonCodigo

2013-02-26 08:17:39 +00:00
Commented Feb 26, 2013 at 8:17 — bonCodigo
– bonCodigo, Commented Feb 26, 2013 at 8:17

mechanical_meat · Accepted Answer · 2013-02-25 22:41:29Z

2

Unless your example is oversimplified, there is no need to use VBA here since you can use SUMPRODUCT to the same effect, e.g.:

=SUMPRODUCT(1*('Raw Data'!K:K=B2))

...and drag down to apply to as many rows as you need.

answered Feb 25, 2013 at 22:41

mechanical_meat

170k25 gold badges238 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2108935 Over a year ago

Thanks. I'll keep this in my tool bag. I'm using vb because the folks I support are doing many manual calculations and I'm trying to make it a easy for them as possible.

Cristian Lupascu · Accepted Answer · 2013-02-25 21:47:42Z

1

The problem is that VBA does not support string interpolation. You need to concatenate instead:

Dim op_id As String For i = 2 To 8 op_id = Sheets("Scorecards").Cells(i, 2) Sheets("Scorecards").Cells(i, 3) = _ "=COUNTIF('Raw Data'!K:K, """ & op_id & """)" Next i

answered Feb 25, 2013 at 21:47

Cristian Lupascu

40.8k18 gold badges107 silver badges139 bronze badges

4 Comments

user2108935 Over a year ago

Didn't know that being a noob. Thanks! Exactly what I wanted.

user2108935 Over a year ago

As a follow-up: Can I somehow nest this or use it when I have multiple criteria? Example: Using the strings (b2:b8) above as well as a couple other columns on the Raw Data sheet. Process: 1. Count the rows with strings above (more info: they are user ids). 2. Count rows containing Fail for strings Pass, Fail for each user id. 3. count rows for critical fail for each fail for each user. Hopefully that makes sense

Cristian Lupascu Over a year ago

@user2108935 I don't know how that would be done using a formula, unless you have a combined column (UserId & Status) and query using countif agains such values (for example 123success, 456fail). There may be a more elegant solution, but I don't see it now

SeanC Over a year ago

@user2108935, if you have excel 2010 or later, COUNTIFS uses multiple criteria

Collectives™ on Stack Overflow

vba pass criteria to countif in variable

2 Answers 2

1 Comment

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Related