Count Values in 2 Cell Ranges

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
974
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
1>Looking for a formula that will count how many numbers match range 1 (DO1:DX1) that are located in range 2 (DO2:DX2)
2>Count if only 1
Example:
DO1 = 13 DX2 = 13
DS1 = 30 DO2 = 30
DU1 = 33 DP2 = 33
DW1= 03 DS2 = 03

3>The formula in essence only counts values in DO1:DX1 only once. 13,10,30,33,00,03
4>The formula in essence only counts values in DO2:DX2 only once. 30,33,31,03,01,03,13

13 appears in both DO1:DX1 and DO2:DX2
10 appears in both DO1:DX1 and NOT in DO2:DX2
30 appears in both DO1:DX1 and DO2:DX2
33 appears in both DO1:DX1 and DO2:DX2
00 appears in both DO1:DX1 and NOT in DO2:DX2
03 appears in both DO1:DX1 and DO2:DX2

5>Final total = 4 values appear in cell range DO1:DX1 and DO2:DX2

Thank you in advance



DO1 = 13
DP1 = 10
DQ1 = 10
DR1 = 13
DS1 = 30
DT1 = 30
DU1 = 33
DV1 = 00
DW1 = 03
DX1 = 03

DO2 = 30
DP2 = 33
DQ2 =31
DR2 = 33
DS2 =03
DT2 = 01
DU2 =03
DV2 = 31
DW2 = 33
DX2 =13
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
How about this

Book1
ABCDEFGHIJK
113101013303033033
230333133313313313
3
4
54
6
7
8
9
Sheet2
Cell Formulas
RangeFormula
A5A5=SUM(--(IFERROR(MATCH(FREQUENCY(A1:J1,A1:J1)^0*TRANSPOSE(A1:J1),TRANSPOSE(A2:J2),0),0)>=1))
 
Upvote 0
Since you have Excel 365, this may also be applicable?

20 07 31.xlsm
ABCDEFGHIJ
113101013303033033
230333133313313313
3
4
54
Count
Cell Formulas
RangeFormula
A5A5=COUNT(UNIQUE(FILTER(A1:J1,COUNTIF(A2:J2,A1:J1)),1))
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top