Forum Discussion
Count the number of cells which have values from another range
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.
SergeiBaklan 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?
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.
SergeiBaklan 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!