Search code examples
excelcriteriavlookupcountif

excel vlookup inside countif criteria

I am trying to combine a countif in excel with the criteria retrieved from a vlookup table. This sounds simple enough, but I am having trouble making it work.

=countif(a1:z1,">=4") I want the column values from a to z counted every time the value is greater than or equal to 4 which is my criteria.

But I don't want to type the four in the above formula. I want it to come from a vlookup.

VLOOKUP("myValue",AA1:DD4,2,FALSE) <--formula would retrieve the 4

Any suggestions how I can do this?

Solution

  • To make the ">=4" a parameter, you just need to do some string building: 

    ">=" & VLOOKUP("myValue",AA1:DD4,2,FALSE)

    The full formula would be:

    =COUNTIF(a1:z1,">=" & VLOOKUP("myValue",AA1:DD4,2,FALSE))

    The & operator is a shorthand for =CONCATENATE(,) which is a long word for "join strings together".