# How to count items in a range containing items from another range.

#### Arie Bos

##### Board Regular
In column A there are strings like “20 US500 (Dec) >2490.1”, or: “30 EURO >1.1918”. So every cell in column C has different content in the description (US500, EURO, Dollar, DAX, NG, CHF, etc) and in the numbers.

In column B, there are 6 different types, say A,B,C,D,E,F

In column C, different values are stored (e.g. C1: EURO, C2: Dollar, C3: Yen etc.).

Now, I want to count the cells in column A, for which Column B contains e.g. “D”, and which cells contain one of the values stored in column C. So in the two string examples above, the first one would not be counted, the second one would, provided there is a “D” in the column B.

I tried to combine COUNTIF with SUMPRODUCT(ISNUMBER(SEARCH(C1:C1000;value))>0, and then embed that in the selection of the D value in column B. Problem is that ‘value’ is not a single cell but should be found in a range (column C) as well, as there are more strings to test for.

Here I got stuck. There are some webpages on ‘If Cells Contain One of Many Things’, but I could’t get it to work.

So, how to count items in a range containing items from another range.

Thanks, Arie

Last edited:

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### istiasztalos

##### Board Regular
Hi,

One solution is to use Array formula instead of SUMPRODUCT and transpose one of the arrays for the SEARCH on multiple cells with multiple criteria. If you don't transpose and both data input for the SEARCH is vertical a.k.a. coming from a column then Excel will do the SEARCH formula one to one with every Nth item in those two parallel arrays. If however one array is horizontal, because switched with a TRANSPOSE, Excel will perform the full array operation to each member of the initial array, hence creating a 2 dimensional array. Luckily for us, it seems that in your example each keyword can only appear once so we don't need to bother eliminating duplicates and can just sum up all appearances.

To include the limitation for type in column B you could use the usual IF array trick to replace matching elements with their original values and set not matching elements to FALSE, Excel will then omit those FALSEs in subsequent calculations.

Long story short this is one way to do what you are looking for, add with CTRL+SHIFT+ENTER since this is an array formula ('keywords' is your list of values in column C that need to be included, 'types' is data in column B and 'strings' is data in column A)

=SUM(--ISNUMBER(SEARCH(keywords,TRANSPOSE(IF(types="d",strings)))))

#### Arie Bos

##### Board Regular
Hi Istiasztalos,

This is a bit above my knowledge of excel to have figured out myself, but I am very lucky with responses from more proficient members on Mr. Excel, like yourself.

I inserted the formula, Thanks to the free great Formuladesk excel add-in it is very simple to see how it works- great utility BTW.
Why does ISNUMBER has two -- in front of it?

There may be more occurrences of the value from keywords in Types.... that is why I would need the sum of them in the total range of what I named Column A in my original question.

Last edited:

#### istiasztalos

##### Board Regular
Hi Arie Bos,

-- is called a "double unary" operator, if you google it there are a lot of more detailed explanations, it is often used with SUMPRODUCT or similar formulas...

Its main purpose is to manage conditional operations, like only do stuff IF this or that condition is true, because when you put it in front of a formula, it will turn TRUE to 1 and FALSE to 0, and you can do then mathematical operations with the 1s and 0s.

What I meant for multiple occurrence, is that
- if you have in column A “30 EURO > 1.1918”, the formula will find it as one occurrence,
- if you have “30 EURO EURO > 1.1918”, it is still one occurrence
- if you have “30 EURO YEN > 1.1918”, it will be counted as two occurrences

#### Arie Bos

##### Board Regular

Hi Istiasztalos,

thank you for the explanation. Never too old to learn!

I made a siple snippet with only the three ranges and the formula (from the more complicated workbook). May be I can email this to you as I spent more time to get it to work, but seems I am missing something.

Thank you again,
Arie

#### istiasztalos

##### Board Regular
Hiya,

You could paste a sample table here and explain what is the error you are getting, or upload an example workbook to Google Drive or MS Onedrive and share it, I can have a look

#### Arie Bos

##### Board Regular

What email address do I use to share with?

#### istiasztalos

##### Board Regular
Hi,

I got your message, checked the file, I can't verify the named ranges and how the formula works because lot of features are not allowed in Excel online and download is not permitted.

I copied however the table to an Excel on my PC, tried the formula and it found correctly the 2

Not sure what could be the difference, maybe your named ranges, what range exactly did you define with them?

#### Arie Bos

##### Board Regular
I found the problem: I had the keywords range as large as the strings and types ranges, so c2:c50. The formula resulted in 2060, instead of 2. When I brought it back to c2:c8, it worked fine.

Thanks a lot!

#### istiasztalos

##### Board Regular
I found the problem: I had the keywords range as large as the strings and types ranges, so c2:c50. The formula resulted in 2060, instead of 2. When I brought it back to c2:c8, it worked fine.

Thanks a lot!

I'm glad that worked have a nice day !

Replies
6
Views
328
Replies
4
Views
219
Replies
5
Views
213
Replies
3
Views
285
Replies
6
Views
155

1,147,497
Messages
5,741,498
Members
423,662
Latest member
Ajmal Khursand

### 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.

### Which adblocker are you using?

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