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

BoloRen

New Member
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.

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

AlphaFrog

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

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

BoloRen

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

Excel Formula:
=SUM(COUNTIF(D318:D321,D327:D328))
Wow, awesome! It worked! Thank you very much, AlphaFrog.

Replies
7
Views
27
Replies
12
Views
105
Replies
5
Views
120
Replies
8
Views
80
Replies
5
Views
118

1,128,159
Messages
5,629,038
Members
416,362
Latest member
Cocito

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back