# Count if no match

#### daveyy

##### New Member
I have a column of data (numbers) from this colum I want to count the numbers that do not appear in the second column. I then need to sum the value in the adjecent column.

Can anybody help? I am tearing my hair out.

Many thanks,

Dave

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Chas17

##### Well-known Member
Could you be more specific?
Give example or post worksheet.
Thanks,
Chas

#### just_jon

##### Legend
Welcome to MrExcel -- a couple of ways -
Book1
ABCD
11111141776
22228881776
3333555
4444345
5555876
6666777
7777999
8888
9999
Sheet1

The 1st formula above is an array formula, which requires entry via Control+Shift+Enter [CSE], not just Enter. Any direct edit of the formula will require re-entry via CSE. You can tell if the formula has been entered correctly, as it will have braces - { } - around it afterward.

#### HalfAce

##### MrExcel MVP
For an example using VBA...
If you have lists of numbers in columns A & B, this will list in column D, the numbers in column A that do not appear in column B and then sum those numbers up at the bottom.
Code:
``````Sub TestSub()
Dim a As Range, x As Range
Application.ScreenUpdating = False

For i = 1 To Range("A65536").End(xlUp).Row
If Application.WorksheetFunction.CountIf(Range("B:B"), Range("A" & i)) = 0 Then
Range("D" & 1 + Range("D65536").End(xlUp).Row) = Range("A" & i)
End If
Next i

Set a = Range("D1", Range("D65536").End(xlUp))
Set x = Range("D65536").End(xlUp)(3, 1)
x.Offset(-1).Value = "Total"
x.Value = WorksheetFunction.Sum(a)

Application.ScreenUpdating = True
End Sub``````
Hope it helps,
Dan

##### MrExcel MVP
daveyy said:
I have a column of data (numbers) from this colum I want to count the numbers that do not appear in the second column. I then need to sum the value in the adjecent column.

...

Agree with Chas. A 5-row sample along with the desired result would help understand your request.

#### daveyy

##### New Member
Thanks all for your help so far. This has been useful, but has not enabled me to solve this problem.

I have a column of numbers in E. The user places numbers in column D. I need to count the number of times a number is entered into column D and is not in Column E. I also need to sum the amounts in columns A B and C where the number in column D is not matched in column E Hopefully this improves on my initial explanation. Thanks once again for all your help.

Dave
Refunds.xls
ABCDE
1NetAmountfeeGrossRefundNLC
20.000.0017491749
30.000.0022221752
40.000.0047014701
50.000.0017464807
60.000.003660
70.000.008187
80.000.001751
90.000.008856
100.000.008881
110.000.001750
120.000.008176
130.000.004808
140.000.003659
Master

##### MrExcel MVP
Thanks for the exhibit. Don't understand why A, B, and C do not house real numbers to calculate with...
Book4
ABCDEFGH
1NetAmountfeeGrossRefundNLC#NotInE2
21114.31217491749SumNetAmount44.2
322.222.615.922221752SumFee35.5
425.111.413.447014701SumGrossRefund36.2
52212.920.317464807
63660
78187
81751
98856
108881
111750
128176
134808
143659
Sheet1

H1:

=SUMPRODUCT(--ISNA(MATCH(D2:D5,E2:E14,0)))

Counts # of the user-entered NLC's that are not in E.

H2:

=SUMPRODUCT(--ISNA(MATCH(D2:D5,E2:E14,0)),A2:A5)

Totals Net Amount's for NLC's that are not in E.

H3:

=SUMPRODUCT(--ISNA(MATCH(D2:D5,E2:E14,0)),B2:B5)

Same as above for Fee.

H4:

=SUMPRODUCT(--ISNA(MATCH(D2:D5,E2:E14,0)),C2:C5)

Same as above for Gross Refund.

#### jindon

##### MrExcel MVP
Hi,

not sure if I understand properly though
the code will display total of the Col. A ,B and C on the correponding row in Col.F
which value in E doesn't match any of the number in col.D
Code:
``````Sub test()
Dim a, lastR As Long, lastR2 As Long, x, i
Dim dic As Object
Set dic = CreateObject("scripting.dictionary")
With ActiveSheet
.Range("f2:f65536").Clear
lastR = .Range("d65536").End(xlUp).Row
lastR2 = .Range("e65536").End(xlUp).Row
ReDim a(1 To lastR2 - 1, 1 To 5)
a = .Range("a2:e" & lastR2).Value
For Each x In .Range("d2:d" & lastR)
Next
For i = LBound(a) To UBound(a)
If Not IsEmpty(a(i, 5)) And Not dic.exists(a(i, 5)) Then
.Cells(i + 1, "f") = a(i, 1) + a(i, 2) + a(i, 3)
End If
Next
End With
Set dic = Nothing
Erase a
End Sub``````
hope this helps

jindon

#### daveyy

##### New Member
Many thanks for this further help.

Your solution Aladin work just fine as long as I dont add further data to columns A, B, C & D. If I extent the formula it will count empty cells. The user will need to add data to these columns as they use the workbook and totals will need to be calculated as further data is entered.
This is a great help and has certainly improved on the situation I was in.
Many thanks again.

Dave

##### MrExcel MVP
daveyy said:
Many thanks for this further help.

Your solution Aladin work just fine as long as I dont add further data to columns A, B, C & D. If I extent the formula it will count empty cells. The user will need to add data to these columns as they use the workbook and totals will need to be calculated as further data is entered.
This is a great help and has certainly improved on the situation I was in.
Many thanks again.

Dave

Extend the formulas with an ISNUMBER conditional...

H1:

=SUMPRODUCT(--ISNA(MATCH(D2:D5,E2:E14,0)),--ISNUMBER(D2:D5))

H2:

=SUMPRODUCT(--ISNA(MATCH(D2:D5,E2:E14,0)),--ISNUMBER(D2:D5),A2:A5)

H3:

=SUMPRODUCT(--ISNA(MATCH(D2:D5,E2:E14,0)),--ISNUMBER(D2:D5),B2:B5)

H4:

=SUMPRODUCT(--ISNA(MATCH(D2:D5,E2:E14,0)),--ISNUMBER(D2:D5),C2:C5)

Replies
7
Views
87
Replies
8
Views
205
Replies
3
Views
92
Replies
13
Views
502
Replies
5
Views
179

1,181,166
Messages
5,928,489
Members
436,604
Latest member
nomi_nomi

### 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.

### Which adblocker are you using?

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

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