Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

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.

share|improve this answer
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

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .