1

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.

Improve this question

2 Answers 2

Reset to default
2

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.

Improve this answer
1
  • If you use SUMPRODUCT in place of both SUM functions you don't need "array entry"
    – barry houdini
    Feb 24, 2014 at 20:30
0

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

Improve this answer

You must log in to answer this question.

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