Arie Bos
Board Regular
 Joined
 Mar 25, 2016
 Messages
 173
 Office Version

 365
 Platform

 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
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: