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

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

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))

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))

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