Calculate a sum based on range criteria

[elsenorjose]

I am creating a Balance Sheet report and need to calculate the sum of a group of accounts based on multiple criteria. I've tried reading up on INDEX and MATCH, but I just can't extend what I've learned to accomplish my task.

I my worksheet, I have columns for Company (Col A), GL Account (Col B), Year (Col C), and Month (Col D to Col O). It's set up as a table from a SQL query and is 'pivoted' so the Month columns are Jan-Dec and have the monthly amounts. In a separate worksheet, a Classified Trial Balance, I have groupings by GL. For example, GLs 1, 3, 7, and 9 are Current Asset accounts, GLs 2, 4, 6, and 8 are Current Liabilities (show in the attached in Columns Q and R). What I need to do is:

Find a specific Company in Col A (Corporate, for example), find specific GL Accounts in Col B (that are matched to the accounts in the Classified TB), find a given year in Col C, sum the monthly values for the given range.

In the worksheet attached, you can see some sample data and expected outcome.

I'm using Excel 2013.

Thank you all in advance for your assistance.

Jose R.

 

[SkipVought]

It's set up as a table from a SQL query and is 'pivoted' so the Month columns are Jan-Dec

This PIVOT report will cause you grief and hardship. Go back and just query the data without the pivot. Then a simple SUMPRODUCT(), which I prefer or SUMIFS(), will accomplish what you want.

How are you getting the resultset into your sheet?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[elsenorjose]

Hi Skip,

The data is coming in using the "From SQL Server" option in the "From Other Sources" selection in the "Data" tab in the ribbon. The main reason is there are so many transactions per month, 1 year's worth of data across 7 companies and 400+ GL accounts per company, resulted in tens of thousands of rows. The developer pivoted the data in a SQL view first, so it could be brought into the sheet summarized by company, gl, year, and month. This reduced the number of rows brought in to the workbook by about 90%.

I tried SUMPRODUCT() and SUMIFS() and got CLOSE, but not quite. For example, using the following, I can get January's sum, but I need to make it more flexible so I don't end up writing 12 of these SUMIFS() to get each month's results.

=SUMPRODUCT(SUMIFS(D221,A2:A21,"Corporate",B2:B21,Q2:Q6,C2:C21,2016))

I researched the CHOOSE() function to see if I could pick the month to sum, but I haven't been able to get that to work.

Jose

 

[SkipVought]

Tens of thousands or even hundreds of thousands of rows is NOT too big a query result to work with in Excel 2007+.

YES, you will need 10 separate SUM... formulas. UGLY!!!

Plus you ALSO need a formula to determine which month. UGLIER!!!

Using A pivot report for data analysis is not a best and accepted data analysis practice for these reasons alone.

Again you'll need merely ONE formula if you have un-pivoted data.

Company, GLAccount, Treansaction Date, Amount

If you prefer, the query could sum by month & year rather than a transaction date. But not a pivot!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Alternatively, you could use the process as outlined in the FAQ below to UN-PIVOT your data and then go from there.
FAQ68-5287.

Might take you 15 minutes.

I'm in an ICU waiting room with my iPad, otherwise I might be able to offer more direct help.

You'll need to first concatenate Company, GLAccount & Year (separating with a unique delimiter) before actually starting the PIVOT Wizard. Then after the un-pivot process, you'll need to parse that field on the delimiter into 3 fields.

Then assuming that your fields are ...

Company, GLAccount, Year, Month, Amount...

your formula might look like...
[tt]
=SUMPRODUCT((Company="West")*(GLAccount=2)*(Year=2016)*(Month="Jan")*(Amount))
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[elsenorjose]

Hi Skip,

Thanks again for your help and direction. Here's the formula that worked. This is using actual cell references so it doesn't match the sample sheet I uploaded, but this formula did the trick. Note that it's an array formula so you have to do the whole CTRL+SHIFT+ENTER to make it work.

=SUM(IF((TBData!A1:A6063="Exeter")*(TBData!A1:U1=CHOOSE(D1,"January","February","March","April","May","June","July","August","September","October","November","December"))*(TBData!AT1:AT6063=A18)*(TBData!I1:I6063=2016),(TBData!A1:U6063)))

Exeter is the company
D1 is a 'parameter' field where you enter the month number you want
A18 has the classification I was looking for in this particular example, with the lookup in TBData column AT
2016 is the year

So, thankfully, no need to unpivot the data and we need just 1 formula for all months.

Thanks again!

Jose

 

[SkipVought]

Great!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]