Vlookup and countif formula

Maykee Source

I have a worksheet consist of multiple Item Templates (Note that 1 template have different values) in in Column A and the item attributes in Column B to Column GD, I want to get the value of "NULL" per item template from Column B:GD. I tried the below formula but it doesn't give the correct value, actually it has an error.

=COUNTIF(A:A,VLOOKUP("*Finished Good*",B:GD,2,0))



answered 2 years ago bzimor #1

For VLOOKUP() function, value which you search should be placed at the first column. Then your formula should be like this:

=COUNTIF(D:GD,VLOOKUP("*Finished Good*",A:GD,4,0))

answered 2 years ago Karpak #2

You can try the below formula, if your requirement is to find number of 'NULL' for 'Finished Goods' - I feel this is your requirement.

=SUMPRODUCT((A3:A100="Finished Goods")*COUNTIF(OFFSET($B$3:$GD$100,(ROW(A3:A100)-3),0,1,COLUMNS($B$3:$GC$100)-1),"NULL")*1)

comments powered by Disqus