Count if no match

daveyy

New Member
Joined
Jan 22, 2005
Messages
18
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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Chas17

Well-known Member
Joined
Oct 16, 2002
Messages
657
Could you be more specific?
Give example or post worksheet.
Thanks,
Chas
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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
Joined
Apr 6, 2003
Messages
9,456
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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
Joined
Jan 22, 2005
Messages
18
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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
Joined
Aug 21, 2004
Messages
16,995
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)
            dic.Add x.Value, Nothing
        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
Joined
Jan 22, 2005
Messages
18
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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)
 

Forum statistics

Threads
1,147,844
Messages
5,743,511
Members
423,800
Latest member
IuneKeiki

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
Top