# Excel: Reference Dropdown Cell in Countif Formula

I'm trying to substitute text in an excel formula with a cell reference to the text in a dropdown.

First formula that yields the correct answer is:

``````=(SUM(COUNTIFS('LP - All Properties'!G:G,{"Phoenix","Tucson"},'LP - All Properties'!L:L,"Leased")))/SUM(COUNTIF('LP - All Properties'!G:G,{"Phoenix","Tucson"}))
``````

I want to replace the text "Phoenix" and "Tucson" with a cell reference to a dropdown containing those values. However, this formula doesn't yield the proper result:

``````=(SUM(COUNTIFS('LP - All Properties'!G:G,{"C2","D2"},'LP - All Properties'!L:L,"Leased")))/SUM(COUNTIF('LP - All Properties'!G:G,{"C2","D2"}))
``````

I also tried without the quotation marks around C2 and D2, but that also did not work.

-

The parameter C2:D2 forms an array and thus you need to make it an array function. The correct formula would be

``````=(SUM(COUNTIFS('LP - All Properties'!G:G,C2:D2,'LP - All Properties'!L:L,"Leased")))/SUM(COUNTIF('LP - All Properties'!G:G,C2:D2))
``````

And then here comes to the tricky part - instead of hitting Enter finishing your input, you will need to press Ctrl+Shift+Enter to let Excel knows it contains parameter to array function.

If you manage to do that correctly, the formula will look like `{=(SUM(COUNTIFS(...))}` with the `{}` parenthesis around the formula.

-
If you use SUMPRODUCT in place of both SUM functions you don't need "array entry" – barry houdini Feb 24 '14 at 20:30

For a slightly different approach you could use `AVERAGE`, i.e. this "array formula"

`=AVERAGE(IF('LP - All Properties'!G:G=C2:D2,IF('LP - All Properties'!L:L="Leased",1,0)))`

confirmed with CTRL+SHIFT+ENTER

This only works when `C2:D2` is a horizontal vector

-