Debit Credit generate list if equal to zero

[allie357]

From my previous posts, I am trying to count the number of unique invoice numbers. However, if the invoice number has debits and credits I have to go through and match the debits and the credits. If the total amount is zero, the invoice number cannot be included in the original count. An example of an invoice number that will need to be removed from my count is the last one in the example list.

I was using this formula to help me find the matching credits and debits but it does not seem to be meeting my needs:
=COUNTIF($H$1:$H1001,$A1&":"&$D1&":"&-$F1)>=COUNTIF($H$1:$H1,$H1)


Can someone help me find a formula that evaluates the invoice number and all the amounts of that number to see if it adds up to zero. Then, if it adds up to zero can it be added to a list? I am new at writing a formula like this.

Book1
	A	B	C	D	E	F
1	VendorName	AccountingDate	Category	InvoiceNum	PONumber	Amount
2	CORPORATEEXPRESSABUHRMANNCO	26-Mar-08	PO	86130822		$1.17
3	CORPORATEEXPRESSABUHRMANNCO	26-Mar-08	PO	86130822		$5.92
4	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$21.35
5	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$8.06
6	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$8.20
7	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$9.64
8	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$12.54
9	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$13.00
10	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$14.88
11	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$17.80
12	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$8.00
13	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$19.28
14	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$4.82
15	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$27.80
16	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$28.92
17	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$40.80
18	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$88.96
19	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$660.96
20	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$18.00
21	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$2.16
22	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$16.30
23	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		($27.80)
24	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$0.51
25	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$0.56
26	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$0.99
27	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$1.32
28	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$6.12
29	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$1.56
30	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$7.84
31	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$2.98
32	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$3.24
33	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$3.59
34	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$4.20
35	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$4.40
36	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$5.82
37	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$7.68
38	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$1.48
39	CORPORATEEXPRESSABUHRMANNCO	31-Mar-08	PO	86282085		$14.78
40	CORPORATEEXPRESSABUHRMANNCO	08-Apr-08	PO	86446484		$101.52
41	CORPORATEEXPRESSABUHRMANNCO	08-Apr-08	PO	86446484		$12.54
42	CORPORATEEXPRESSABUHRMANNCO	08-Apr-08	PO	86446484		$75.26
43	CORPORATEEXPRESSABUHRMANNCO	08-Apr-08	PO	86446484		$71.87
44	CORPORATEEXPRESSABUHRMANNCO	08-Apr-08	PO	86446484		$55.60
45	CORPORATEEXPRESSABUHRMANNCO	08-Apr-08	PO	86446484		$27.80
46	CORPORATEEXPRESSABUHRMANNCO	08-Apr-08	PO	86446484		$20.52
47	CORPORATEEXPRESSABUHRMANNCO	08-Apr-08	PO	86446484		$20.40
48	CORPORATEEXPRESSABUHRMANNCO	08-Apr-08	PO	86446484		$20.09
49	CORPORATEEXPRESSABUHRMANNCO	08-Apr-08	PO	86446484		$18.66
50	CORPORATEEXPRESSABUHRMANNCO	08-Apr-08	PO	86446484		$7.77
51	CORPORATEEXPRESSABUHRMANNCO	08-Apr-08	PO	86446484		$84.00
52	CORPORATEEXPRESSABUHRMANNCO	08-Apr-08	PO	86446484		$17.80
53	CORPORATEEXPRESSABUHRMANNCO	08-Apr-08	PO	86446484		$12.21
54	CORPORATEEXPRESSABUHRMANNCO	08-Apr-08	PO	86446484		$11.44
55	CORPORATEEXPRESSABUHRMANNCO	08-Apr-08	PO	86446484		$7.55
56	CORPORATEEXPRESSABUHRMANNCO	08-Apr-08	PO	86446484		$7.55
57	CORPORATEEXPRESSABUHRMANNCO	08-Apr-08	PO	86446484		$7.47
58	CORPORATEEXPRESSABUHRMANNCO	08-Apr-08	PO	86446484		$4.71
59	CORPORATEEXPRESSABUHRMANNCO	08-Apr-08	PO	86446484		($14.00)
60	CORPORATEEXPRESSABUHRMANNCO	08-Apr-08	PO	86446484		($550.80)
61	CORPORATEEXPRESSABUHRMANNCO	08-Apr-08	PO	86446484		$17.45
62	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$12.21
63	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$113.70
64	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$20.09
65	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$13.56
66	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$14.00
67	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$14.46
68	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$15.72
69	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$17.45
70	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$12.68
71	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$24.10
72	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$24.50
73	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$25.00
74	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$30.38
75	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$35.28
76	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$113.70
77	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$11.27
78	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$35.21
79	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$32.00
80	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$1.44
81	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$11.00
82	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$76.20
83	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$0.68
84	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$1.74
85	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$1.95
86	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$2.39
87	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$3.08
88	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$3.50
89	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$3.96
90	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$4.98
91	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$6.09
92	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$6.23
93	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$9.64
94	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$6.48
95	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$7.89
96	CORPORATEEXPRESSABUHRMANNCO	15-Apr-08	PO	86584465		$3.59
97	CORPORATEEXPRESSABUHRMANNCO	22-Apr-08	PO	86780760		($25.00)
98	CORPORATEEXPRESSABUHRMANNCO	22-Apr-08	PO	86780760		$69.18
99	CORPORATEEXPRESSABUHRMANNCO	22-Apr-08	PO	86780760		$52.24
100	CORPORATEEXPRESSABUHRMANNCO	22-Apr-08	PO	86780760		$24.16
101	CORPORATEEXPRESSABUHRMANNCO	22-Apr-08	PO	86780760		$8.20
102	CORPORATEEXPRESSABUHRMANNCO	22-Apr-08	PO	86780760		$3.14
103	CORPORATEEXPRESSABUHRMANNCO	22-Apr-08	PO	86780760		$1.53
104	CORPORATEEXPRESSABUHRMANNCO	22-Apr-08	PO	86780760		($7.00)
105	CORPORATEEXPRESSABUHRMANNCO	22-Apr-08	PO	86780760		($16.00)
106	CORPORATEEXPRESSABUHRMANNCO	22-Apr-08	PO	86780760		($12.25)
107	CORPORATEEXPRESSABUHRMANNCO	06-May-08	PO	87119050		$58.00
108	CORPORATEEXPRESSABUHRMANNCO	06-May-08	PO	87119050		$8.62
109	CORPORATEEXPRESSABUHRMANNCO	06-May-08	PO	87119050		$4.80
110	CORPORATEEXPRESSABUHRMANNCO	06-May-08	PO	87119050		$6.23
111	CORPORATEEXPRESSABUHRMANNCO	06-May-08	PO	87119050		$70.01
112	CORPORATEEXPRESSABUHRMANNCO	06-May-08	PO	87119050		$4.98
113	CORPORATEEXPRESSABUHRMANNCO	06-May-08	PO	87119050		$4.12
114	CORPORATEEXPRESSABUHRMANNCO	06-May-08	PO	87119050		$1.85
115	CORPORATEEXPRESSABUHRMANNCO	06-May-08	PO	87119050		$44.09
116	CORPORATEEXPRESSABUHRMANNCO	06-May-08	PO	87119050		$4.71
117	CORPORATEEXPRESSABUHRMANNCO	19-May-08	PO	87436043		$36.92
118	CORPORATEEXPRESSABUHRMANNCO	19-May-08	PO	87436043		$10.26
119	CORPORATEEXPRESSABUHRMANNCO	19-May-08	PO	87436043		$6.59
120	CORPORATEEXPRESSABUHRMANNCO	19-May-08	PO	87436043		$1.25
121	CORPORATEEXPRESSABUHRMANNCO	27-May-08	PO	87587066		$6.17
122	CORPORATEEXPRESSABUHRMANNCO	27-May-08	PO	87587066		$2.90
123	CORPORATEEXPRESSABUHRMANNCO	27-May-08	PO	87587066		$3.93
124	CORPORATEEXPRESSABUHRMANNCO	27-May-08	PO	87587066		$4.80
125	CORPORATEEXPRESSABUHRMANNCO	27-May-08	PO	87587066		$1.61
126	CORPORATEEXPRESSABUHRMANNCO	27-May-08	PO	87587066		$8.10
127	CORPORATEEXPRESSABUHRMANNCO	27-May-08	PO	87587066		$8.18
128	CORPORATEEXPRESSABUHRMANNCO	27-May-08	PO	87587066		$29.00
129	CORPORATEEXPRESSABUHRMANNCO	27-May-08	PO	87587066		$113.70
130	CORPORATEEXPRESSABUHRMANNCO	27-May-08	PO	87587066		$151.57
131	CORPORATEEXPRESSABUHRMANNCO	27-May-08	PO	87587066		$4.80
132	CORPORATEEXPRESSABUHRMANNCO	27-May-08	PO	87587066		$3.63
133	CORPORATEEXPRESSABUHRMANNCO	27-May-08	PO	87587066		$1.44
134	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$19.51
135	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$5.98
136	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$6.23
137	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$6.26
138	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$6.90
139	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$7.32
140	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$22.14
141	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$7.96
142	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$18.36
143	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$5.39
144	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$7.16
145	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$17.67
146	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$17.20
147	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$14.54
148	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$13.50
149	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$12.50
150	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$10.19
151	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$8.98
152	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$0.99
153	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$7.96
154	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$4.84
155	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$0.72
156	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$0.34
157	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$0.99
158	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$1.52
159	CORPORATEEXPRESSABUHRMANNCO	03-Jun-08	PO	87709690		$2.31
160	CORPORATEEXPRESSABUHRMANNCO	02-Aug-07	PO	18245U	766628	$9,433.84
161	CORPORATEEXPRESSABUHRMANNCO	02-Aug-07	PO	18245U	766628	($9,433.84)
Sheet1

 

[DonkeyOte]

If you want to generate a distinct listing rather than merely a count have you thought of using a Pivot Table and filter out those Invoices where Sum of Amount = 0 ?

EDIT: on an aside if is just a COUNT you want (and you don't want a PT (though this would be my advice)) then something like:

=SUMPRODUCT(--(SUMIF($D$2:$D$161,$D$2:$D$161,$F$2:$F$161)<>0),1/COUNTIF($D$2:$D$161,$D$2:$D$161))

(where range of data is 2:161)