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

BoloRen

New Member
Joined
Dec 17, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

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

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
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
Joined
Dec 17, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top