excel vlookup inside countif criteria

1.4k views Asked by At

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?

1

There are 1 answers

0
Byron Wall On BEST ANSWER

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".