Microsoft Excel - how to combine COUNTIF with VLOOKUP function

Asked By AJ on 18-Aug-11 12:18 PM
Hi

I am using the VLOOKUP function to lookup values in several worksheets in my workbook and return the proper value.

Now I need to use countif to find out how many of those values (in the same cell on each worksheet) are greater than zero

Here is my Vlookup formula being used with the SUM function - and this works fine
=SUM(VLOOKUP(B2,'week one'!$B$2:$P$22,14,FALSE),VLOOKUP(B2,'week two'!$B$2:$P$22,14,FALSE),VLOOKUP(B2,'week three'!$B$2:$P$22,14,FALSE),VLOOKUP(B2,'week four'!$B$2:$P$22,14,FALSE),VLOOKUP(B2,'week five'!$B$2:$P$22,14,FALSE))


Now I have to use the COUNTIF>1
THAT IS TO SAY, COUNT EACH OF THESE LOOKUP VALUES IF THEY ARE GREATER THAN ONE

any help greatly appreciated

AJ
Devil Scorpio replied to AJ on 18-Aug-11 12:47 PM
Hi,

It is possible to combine both function COUNTIF and VLOOKUP in excel

Have a look in following example

=COUNTIF($I$51:$I$253,VLOOKUP(B41,B:C,2,0))+COUNTI F('TSection'!$I:$I,VLOOKUP(B41,B:C,2,0))+COUNTIF(' BSection'!$I:$I,VLOOKUP(B41,B:C,2,0))
AJ replied to AJ on 18-Aug-11 02:08 PM

thanks for your answers, so far, can't fix it.

I can make the SUM function work using VLOOKUP to look in five worksheets.

Cannot make countif work