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

[xcelnovice101]

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.

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

 

[Jonmo1]

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]

This is a working formula using sumproduct

=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]

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

 

[xcelnovice101]

ADVERTISEMENT

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]

The short answer is YES.

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]

Here is an example Countif based on result of Vlookup

Excel Workbook
	A	B
1	Countif Range
2	a
3	a
4	b
5	b
6	b
7	c
8
9
10	Vlookup Table
11	Jon	a
12	George	b
13	Alice	c
14
15	Formula
16	3
Sheet1