Hi - Dave here.
One problem that comes up a lot in Excel is how to compare two ranges and count values that appear in both.
To solve this problem, you can use the COUNTIF function or the MATCH function, with help from our friend SUMPRODUCT to tally up results.
In the worksheet shown, the formula in F5 is:
=SUMPRODUCT(COUNTIF(range1,range2))
where
range1 (B5:B16) and
range2 (D5:D13) are named ranges.
[
download the workbook and read the full explanation]
As I mentioned, you can also use the MATCH function to solve this problem. The article explains both approaches and includes the Excel workbook. Download the workbook and try it out yourself.
Excel formulas
We maintain a large list of working formulas
here.
If you need more structure, we also offer
video training.
Have a fun and safe weekend!
Dave
