Getting Totals in 2 Columns

**MotoDave** · 08-23-2012, 10:35 AM

Hello,

I'm working on a report for loan insurance information. I'm getting my data from the results of a SQL Server query. Loan numbers in the results may come back multiple times which is to be expected becuase a loan can have more than one insurance policy attached to it. Here's where I need some help. There are 2 columns with dollar amounts. The first column contains the current balance on the loan and the 2nd column is for the coverage amount(s) of the insurance policy. If a loan were to come back 3 times (due to 3 insurance policies) then I will get the 3 seperate amounts for each policy but I also get the current balance amount 3 times as well. If the amounts in the Coverage Amount column are added the result would be the correct total for the 3 insurance policies on the loans, but if the Current Balance is added up it would be incorrect because there really is only one current balance but since it's in the results 3 times the total would be tripled. Once I get my SQL results into Excel how can I set up the spreadsheet so the current balance only shows (gets totaled) once but the coverage amount show 3 times and gets totaled 3 times. This way when those columns are summed the totals will be correct. Of course some loans are only in the results once since they only have one policy. I have attached an example spreadsheet.

Any help will be greatly appreciated.

Thank you.

Dave

**DBY** · 08-23-2012, 11:23 AM

Hello Dave
I've only tried this out on the sample data you supplied but try the following formulas:

For the 'Coverage Amount', copied down an empty column:

Please Login or Register  to view this content.

And for the 'Current Balance':

Please Login or Register  to view this content.

Test them out on a bigger data set and see if they perform OK. Take care with the Absolute and Relative cell references.

Hope this helps.
DBY

**rlb278** · 08-23-2012, 11:38 AM

SUMIF(Array of Account Numbers,Account Number,Coverage Amount)
SUMIF(A1:A6,Ref Cell,I1:I6)

Vlookup(Account Number, Entire Chart,Column Reference, Exact Match)
Vlookup(Ref Cell, A1:L6, 12, FALSE)

See attached