Counting appearances in ranges

  • Thread starter Thread starter druk
  • Start date Start date
D

druk

I have two sheets. In each sheet there are values.

I need a formula that will output how many times a value in the firs
sheet appears in the second sheet.

I suppose the formula must take two ranges, and then compare each cel
in the first range to each cell in the second range. For each match, i
will count 1, so that it will output the total number of matches.

There is a catch, though. The values in the first sheet are numbers bu
in text format, because I need to enter numbers like 0060.

In the second sheet, the values are in the following format: fou
numbers, dash, one letter, dash, two numbers, e.g. 0060-D-03.

The formula must count a match when the value in the first rang
matches the first four caracters of the value in the second range.

Example: cell in the first range containing 2030, cell in second rang
containing 2030-D-02, this is a match.

Also, I need it to be a formula, not a macro, because I need it t
update the output whenever I enter new values in any of the sheets.

Thank you
 
Hi
on your firstsheet enter the following in B1 (if column a contains your
values):
=COUNTIF('sheet2'!$A$1:$A$1000,A1 & "*")
and copy down
 
That's a nice approach, but the data in the first sheet is severa
columns wide, so I would need something like a1:z1 & "*", which o
course doesn't work.

To clarify: several cells in sheet 1 / row 1 have to be compare
against a range in sheet 2, one at a time, so to speak.

So if you have:

Sheet 1
A1 B1 C1 D1
10 20 30 40

Sheet 2
D3 Z300
20-D-04 10-D-04

The formula should yield 2.
 
Hi
try
=SUM(COUNTIF('sheet2'!$A$1:$A$1000,A1:G1 & "*"))
entered as array formula (with CTRL+SHIFT+ENTER
 
Back
Top