Using IF or SUMIF function to find discrepancies

**paperwings25** · 08-18-2011, 12:07 PM

I have a file with 121,000 lines of data in Excel 2007. The relevant columns above are Debit, Credit and Policy Number. When I sum all at the bottom, there is a discrepancy between Debits and Credits when there shouldn't be. I am trying to figure out a way to detect which policy #'s debit and credits do not match. I have concluded that it is probably going to be an IF or SUMIFS function, but have been beating my head to crunch out a formula that will compute which policy has their debits and credits not matching. Any help is greatly appreciated! Thanks!

**NBVC** · 08-18-2011, 01:07 PM

Say the columns are in A:C (Policy Number, Debit, Credit, respectively)

Then in D2 you can try something like:

=SUMIF(A:A,A2,B:B)=SUMIF(A:A,A2,C:C)

copied down

This will sum all Debits for the Policy in A2 and compare to the Sum of all Credits in column C... TRUE means the sums match.

if the Debits are negative, you will need to negate one of the Sumifs.

**paperwings25** · 08-19-2011, 08:44 AM

Would inserting a pivot table be of any help on this you think?

**NBVC** · 08-19-2011, 08:49 AM

Yes, I think it might be quicker, if it reduces the list size significantly then it will be easy, but if not you may still need an added column in the Pivot table (maybe a calculated field) to determine differences.

**paperwings25** · 08-19-2011, 08:53 AM

Originally Posted by NBVC

Say the columns are in A:C (Policy Number, Debit, Credit, respectively)

Then in D2 you can try something like:

=SUMIF(A:A,A2,B:B)=SUMIF(A:A,A2,C:C)

copied down

This will sum all Debits for the Policy in A2 and compare to the Sum of all Credits in column C... TRUE means the sums match.

if the Debits are negative, you will need to negate one of the Sumifs.

This will not work b/c there are multiple debits per policy and sometimes only 1 to 2 credits to offset the debits. For instance, B1-B5 are 5,10,10,5,10, and there's 2 credits of 25 and 15.

**NBVC** · 08-19-2011, 09:58 AM

SUMIF consolidates and sums all of them together.. my suggestion was to compare the sums.

**paperwings25** · 08-23-2011, 10:20 AM

NVBC, you were right with the SUMIF. My formula ended up being "=SUMIF(ereports!$K$5:$K$9990,$A8,ereports!$J$5:$J$9990)-SUMIF(ereports!$K$5:$K$474,$A8,ereports!$I$5:$I$474)" where my ereports sheet, K5:K9990 had all my policies, A8 was my policy # on another sheet, and J and I were my debits and credits, respectively. This enabled me to see which policy had unbalanced debits and credits. Thanks!!