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

[Arie Bos]

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

 

[istiasztalos]

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]

Hi Istiasztalos,

thanks a lot for your elaborate answer.

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.

 

[istiasztalos]

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]

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]

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]

What email address do I use to share with?

 

[istiasztalos]

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]

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]

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 !