comare totals between tables one to many relationship

[jrg24]

Hello All.
I am sure this is an easy thing but I cannot seem to wrap my head around it. I am also sure this has been asked before but I am not certain how to articulate it in a search.

I have two tables, Table 1 is a list of invoices that contains the total cost related to the invoice (This is the one side). Table 2 lists each individual cost line broken out (each cost is a separate record). Invoice number is the related field. What I would like to do is compare the sum of the individual cost lines in table 2 to the total cost in table 1 and return all records from table 2 that do not add up to the value on table 1.

Thanks for any help you can provide.

 

[BlueHornet]

Maybe something like this, using the SUMPRODUCT() function?

	A	B	C	D	E	F	G	H	I
1	Invoice	Amount			Invoice
2	A123	$ 123.45			A123	$ 100.00			Perfect match for A123
3	B124	$ 567.89			A123	$ 23.45			No Match for B124
4	C888	$ 1,000.00			B124	$ 500.00			Perfect match for C888
5					B124	$ 67.00
6					C888	$ 250.00
7					C888	$ 250.00
8					C888	$ 250.00
9					C888	$ 250.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula I2 =IF( B2 <> SUMPRODUCT(( F2:F9) * ( E2:E9 = A2)), "No Match for ", "Perfect match for ") & A2 I3 =IF( B3 <> SUMPRODUCT(( F3:F10) * ( E3:E10 = A3)), "No Match for ", "Perfect match for ") & A3 I4 =IF( B4 <> SUMPRODUCT(( F4:F11) * ( E4:E11 = A4)), "No Match for ", "Perfect match for ") & A4 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>