What's wrong with this formula? (Cell references in an array)

[BoloRen]

Hi everyone. I've been trying to include cell references in an array, but the formula is either invalid or returns incorrect results.

First, I have the range D318:D321, where the cells contain the numbers "18", "19", "18" and "16" respectively. Then I have the input fields D327 and D328, where I assigned "18" to the former, and "17" to the latter. What I'm trying to achieve is to set up a formula that counts how many of the numbers in cells D327 and D328 appear in range D318:D321. So the correct result in this case should be "2" as "18" (D327) appears twice, and "17" (D328) appears zero times. However, when I enter the following formula, Excel returns "0", and I'm not sure why:

=SUM(COUNTIF(D318:D321,"{"&D327&","&D328&"}"))

Note that if I type =SUM(COUNTIF(D318:D321,{"18","17"})), it works fine, but typing in the numbers manually is exactly the hassle I'm trying to avoid as I have a large number of input items and cell ranges to work with, and I need to update those inputs from time to time without having to bother changing the numbers accordingly in each and every formula.

I'm sorry if my question is very basic, but I'm really eager to know the answer. Any help is greatly appreciated.

 

[AlphaFrog]

Try this. Confirm with Ctrl+Shift+Enter
Excel inserts curly brackets at the beginning and end of the formula for you.

=SUM(COUNTIF(D318:D321,D327:D328))

 

[BoloRen]

Try this. Confirm with Ctrl+Shift+Enter
Excel inserts curly brackets at the beginning and end of the formula for you.

=SUM(COUNTIF(D318:D321,D327:D328))

Wow, awesome! It worked! Thank you very much, AlphaFrog.