Count if no match

[daveyy]

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

 

[Chas17]

Could you be more specific?
Give example or post worksheet.
Thanks,
Chas

 

[just_jon]

Welcome to MrExcel -- a couple of ways -

Book1
	A	B	C	D
1	111	114	1776
2	222	888	1776
3	333	555
4	444	345
5	555	876
6	666	777
7	777	999
8	888
9	999
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]

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.

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]

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]

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
	A	B	C	D	E
1	NetAmount	fee	GrossRefund	NLC
2	0.00		0.00	1749	1749
3	0.00		0.00	2222	1752
4	0.00		0.00	4701	4701
5	0.00		0.00	1746	4807
6	0.00		0.00		3660
7	0.00		0.00		8187
8	0.00		0.00		1751
9	0.00		0.00		8856
10	0.00		0.00		8881
11	0.00		0.00		1750
12	0.00		0.00		8176
13	0.00		0.00		4808
14	0.00		0.00		3659
Master

 

[Aladin Akyurek]

Thanks for the exhibit. Don't understand why A, B, and C do not house real numbers to calculate with...

Book4
	A	B	C	D	E	F	G	H
1	NetAmount	fee	GrossRefund	NLC			#NotInE	2
2	11	14.3	12	1749	1749		SumNetAmount	44.2
3	22.2	22.6	15.9	2222	1752		SumFee	35.5
4	25.1	11.4	13.4	4701	4701		SumGrossRefund	36.2
5	22	12.9	20.3	1746	4807
6					3660
7					8187
8					1751
9					8856
10					8881
11					1750
12					8176
13					4808
14					3659
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]

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

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]

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]

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)