Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.