Count the number of cells which have values from another range

[Gyula Nagy]

Hi all,

I have a huge amount of cells contain numbers in E3:CY2
I have a range of numbers in A2:A1230
I would like to count the number of cells from E3:CY2 where the cell value is present in A2:A1230.
My first idea was with COUNTIF and VLOOKUP but for VLOOKUP it is not possible to give more than 1 cell value to check.
I also found a possible solution with SUMPRODUCT(COUNTIF()) which works nice in case of text values but as the cells contains numbers excel multiply them as intended.
Do you have any idea how to solve this?

Thanks in advance,
Gyula

[#2]

Hi Gyula,

IMHO,

=SUMPRODUCT(COUNTIF(E2:CY3,A2:A1230))

shall return correct result, as well as equivalent array formula

={SUM(COUNTIF(E2:CY3,A2:A1230))}

[#3]

---

@Sergei Baklan wrote:

Hi Gyula,

IMHO,

=SUMPRODUCT(COUNTIF(E2:CY3,A2:A1230))

shall return correct result, as well as equivalent array formula

={SUM(COUNTIF(E2:CY3,A2:A1230))}

---

Thanks for the reply but it seems none of them working.

The problem with the first option is that by default if a cell has a numeric value "SUMPRODUCT" multiple those values. I just need to count the number of cells which have matching values in A2:A1230.
The second one simply did not work.
I translated the function names to my (excel's) native language, also replaced the "," with ";" and play some with the "{" "(" without success.
This is what I would like to do:
COUNTIF(E2:CY2,VLOOKUP(E2:CY2,A2:A1230,1,0))
except of course "E2:CY2" do not work as a single value… :(
Is there any function which is able to lookup for every single value in a given range against another range?

[#4 · Accepted solution]

Hi Gyula,

These two formulas give exactly the same result, the only the one with SUM you shall use as array formula, that means entering it you press not Enter but keep pressed Ctrl and Shift, after that Enter and release all 3 buttons.

SUPRODUCT in this case just allow to avoid array formula, it multiplies on nothing.  If we take that small sample

if we evaluate SUMPRODUCT, result after COUNTIF will be

=SUMPRODUCT({0;2;1;0})

and after that the formula sums the array. The same as do one by one at the left side.

Please see the sample attached.

[#5]

---

@Sergei Baklan wrote:

Hi Gyula,

These two formulas give exactly the same result, the only the one with SUM you shall use as array formula, that means entering it you press not Enter but keep pressed Ctrl and Shift, after that Enter and release all 3 buttons.

SUPRODUCT in this case just allow to avoid array formula, it multiplies on nothing.  If we take that small sample

if we evaluate SUMPRODUCT, result after COUNTIF will be

=SUMPRODUCT({0;2;1;0})

and after that the formula sums the array. The same as do one by one at the left side.

Please see the sample attached.

---

Thank you a lot!!
Finally it works like a miracle!