Arie Bos
Board Regular
- Joined
- Mar 25, 2016
- Messages
- 224
- 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: