# Is it possible to use COUNTIF in conjunction with VLOOKUP in VBA?

#### xcelnovice101

##### Active Member
I am trying execute a VLOOKUP and counting a specific cell, if it's data is greater than 0 for nine different files, essentially summing the number of data points. Below is the beginning of my formula.

Code:
``Range(Range("W8"), Range("W" & Analyst)).Formula = "=COUNTIF(VLOOKUP(\$A8,'" & Folder & "[" & Barker & "]" & A & "'!\$A8:W500,23,0),"">0"")+COUNTIF(...``

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### Jonmo1

##### MrExcel MVP
To be clear here...
VBA is NOT using Vlookup or Countif there.
The only thing VBA is doing is putting a formula into a cell using a Text String that represents the formula you want.

to better answer this question, let's forget about VBA for a moment.
Let's just go into the worksheet and see if we can manually enter a formula that does what you want.
THEN we can go back to VBA and make it do the formula for you.

So what would the resulting formula you want look like in a Cell ?

#### xcelnovice101

##### Active Member
This is a working formula using sumproduct

Code:
``=SUMPRODUCT(--([Barker.Sxlsm]Analyst!\$A\$8:\$A\$54=A8)*(--([Barker.Sxlsm]Analyst!\$V\$8:\$V\$54>0)))``

Now I just need to Dim the range and add the eight remaining files to this formula.

#### Jonmo1

##### MrExcel MVP
So where does Vlookup and/or Countif come into it?

#### xcelnovice101

##### Active Member
Can I do a COUNTIF based on the results of a VLOOKUP? Everything I'm finding on-line says to use SUMPRODUCT for this. Should I start a new post under SUMPRODUCT?

#### Jonmo1

##### MrExcel MVP

The long answer is that you're not providing enough information for anyone to formulate any kind of suggestion to resolve your issue.
See here

#### Jonmo1

##### MrExcel MVP
Here is an example Countif based on result of Vlookup

Excel Workbook
AB
1Countif Range
2a
3a
4b
5b
6b
7c
8
9
10Vlookup Table
11Jona
12Georgeb
13Alicec
14
15Formula
163
Sheet1

Replies
3
Views
455
Replies
0
Views
194
Replies
6
Views
134
Replies
13
Views
249
Replies
2
Views
189

1,195,600
Messages
6,010,650
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back