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

Arie Bos

Board Regular
Joined
Mar 25, 2016
Messages
173
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 18, 2016
Messages
135
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
Joined
Mar 25, 2016
Messages
173
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

istiasztalos

Board Regular
Joined
May 18, 2016
Messages
135
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
Joined
Mar 25, 2016
Messages
173
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 18, 2016
Messages
135
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
Joined
Mar 25, 2016
Messages
173
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What email address do I use to share with?
 

istiasztalos

Board Regular
Joined
May 18, 2016
Messages
135
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
Joined
Mar 25, 2016
Messages
173
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 18, 2016
Messages
135
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 !
 

Forum statistics

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