Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. Member
Join Date
16th November 2005
Location
NSW Australia
Posts
49

## SUMIF on Badly Laid out Data

I have a spreadsheet where my source data is a Profit & Loss exported to Excel from an accounting package. Unfortunately the layout of the exported Profit & Loss report is very poor to facilitate data manipulation. I'm unable to change the layout of this report (as the accounting program doesn't give the option and the users of this spreadsheet do not have the time nor skill to reformat the layout of the Excel export into something more useable).

An inconvenient but manageable problem is the account number is merged into the same cell as the account description (which makes account number lookups awkward). The main problem, however, is the monthly amount columns for the year are not adjacent to each other - instead they alternate with the comparative monthly column from the prior year (ie July This Year; July Last Year; August This Year; August Last Year, September This Year etc).

I need to be able to do the equivalent of a SUMIF across multiple columns so that I can add the balances for a specified number of months for those account numbers that fall within a certain range (eg sum all accounts between codes 200 and 212 inclusive for months October to December inclusive for the current year).

If the monthly columns for the year were adjacent to each other I could use an array formula, however, I can't see how to do this with any combination of Excel's native formulas. My only solution has been to design a custom function (UDF) but it has slowed the spreadsheet down a fair bit.

I've attached a cut-down sample version for anybody who might have some alternatives to using a UDF. Failing that, any suggestions on how to improve my UDF would be greatly appreciated.

My UDF code is below:

VB:
```Function SumRangeLookup(FromCode, ToCode, Database, FromColumn, ToColumn) As Double

Dim Code As Range
Dim MonthColumns As Integer
Dim RowCount As Integer
Dim CalcResult As Double

CalcResult = 0
RowCount = 0

'Lookup code and sum the corresponding month(s) amount if the code is between the code range
For Each Code In Range(Database)
RowCount = RowCount + 1
On Error Goto SkipCode
If Code >= FromCode And Code <= ToCode Then
For MonthColumns = FromColumn To ToColumn Step 2
CalcResult = CalcResult + Worksheets("Source Data").Range("A1").Offset(RowCount - 1, MonthColumns).Value
Next MonthColumns
End If
Next Code

SumRangeLookup = CalcResult

SkipCode:

End Function

```
Last edited by VBA New Kid; September 11th, 2006 at 23:31.

Excel Video Tutorials / Excel Dashboards Reports

2. Senior Member
Join Date
29th July 2006
Posts
224

## Re: Alternative Solutions to SUMIF Custom Function/UDF Required

Originally Posted by VBA New Kid
I have a spreadsheet where my source data is a Profit & Loss exported to Excel from an accounting package. Unfortunately the layout of the exported Profit & Loss report is very poor to facilitate data manipulation. I'm unable to change the layout of this report (as the accounting program doesn't give the option and the users of this spreadsheet do not have the time nor skill to reformat the layout of the Excel export into something more useable).
If this is so why don't you write a macro to open these files and re arange them into a usable format for all staff and to make it easier to use.

you would only need a folder to dump all exports into and a simple macro to open them one by one and reformat and adding any further calcs needed before distribution.

just an idea.

Excel Video Tutorials / Excel Dashboards Reports

3. ## Re: Alternative Solutions to SUMIF Custom Function/UDF Required

Would it not be possible to IMPORT the data to Excel ? It is with many packages, using Jet, ODBC etc

Can you tell us what you're using? There are ALOT of accountants that hang out here so you may find someone's done this already

Most Accounting packages have a "friendly" method for getting data to Excel nowadays.
Last edited by Will Riley; September 12th, 2006 at 00:09.

4. Member
Join Date
16th November 2005
Location
NSW Australia
Posts
49

## Re: Alternative Solutions to SUMIF Custom Function/UDF Required

The accounting package is an Australian and New Zealand based program called BankLink that is typically only used by public accountants as a bookkeeping tool for their business clients. The reports that it generates are very, very basic and not at all presentable for clients.

What I am trying to do is develop some Excel-based management reports with some graphs so that various accounting firms can provide some better reporting to their clients.

I have spoken with the software developers at BankLink and have been told that they use a "closed database" which prevents users from gaining access to the data from external programs such as Excel - therefore there's no ODBC etc links sorry Will. The only way to do anything is to generate the report within BankLink then export it to Excel in the format in the file that I attached in my original post.

My concern in writing macros to re-configure the data into a more useable format is the impact on the file size and also the time that it would take to execute. These concerns are from the end-user's perspective as the accounting firms have neither the time nor patience to wait for lengthy macros to process and the staff using the spreadsheet will typically be inexperienced staff so I need to make everything as simple as possible. Its also highly likely that they will replicate this file hundreds of times for their many clients so a large file size would be magnified many times and use of a lot of file server space. If a solution can be found to keep the file size down and to make the macro execution speedy, maybe this would result in a better layout and therefore avoid the need for UDF's???

Hopefully this gives you a better background on the problem and some of the stumbling blocks.

Excel Video Tutorials / Excel Dashboards Reports

5. ## Re: Alternative Solutions to SUMIF Custom Function/UDF Required

Shame about the "bag of [email protected]" software ...

Well, is there any way that you could illustrate what gets exported (i.e. the crappily formatted report) and how you would ideally like it to look in order to do your calcs.

Then we could come up with some sort of solution

6. Established Member
Join Date
24th March 2005
Posts
507

## Re: SUMIF on Badly Laid out Data

Hi VBA New Kid,

I think this might work for you. Input formula in cell C3 in worksheet Calculation.

=SUMPRODUCT((LEFT('Source Data'!A10:A19,3)>=A3)*(LEFT('Source Data'!A10:A19,3)<=B3)*ISNUMBER(MATCH('Source Data'!B6:X6,\$B\$6:\$B\$7,0))*('Source Data'!B10:X19))

Please look at the sample workbook below.
Let me know how it turns out.
Last edited by Fin Fang Foom; September 12th, 2006 at 23:13.

Excel Video Tutorials / Excel Dashboards Reports

7. Member
Join Date
16th November 2005
Location
NSW Australia
Posts
49

## Re: SUMIF on Badly Laid out Data

This is a pretty good job thanks Fin Fang Foom, however, it errors when refering to rows outside of the data range (note that the formula needs to refer to an expanded data range as the size of the data will vary depending on the size of the exported report). Also, your formula assumes a fixed account code length of 3, however, the account code can vary between 3 and 7 and will quite often have a character (eg "/") to delineate between the account code and the sub-code.

Also, your formula only selects the start and end months and doesn't include any months in between.

Thanks for your suggestion though as I've been able to build on it to cater for the first of the two abovementioned problems. My expanded formula can be seen the attached file in my next post.

Excel Video Tutorials / Excel Dashboards Reports

8. Member
Join Date
16th November 2005
Location
NSW Australia
Posts
49

## Re: Alternative Solutions to SUMIF Custom Function/UDF Required

Originally Posted by Will Riley
Shame about the "bag of [email protected]" software ...

Well, is there any way that you could illustrate what gets exported (i.e. the crappily formatted report) and how you would ideally like it to look in order to do your calcs.

Then we could come up with some sort of solution
I've attached a sample file that has both the original exported source data (with the poor layout) and my manually adjusted source data (which I think is a more user friendly layout whereby the monthly columns are grouped together into their respective years instead of the original version which had the months alternating between each year).

In the Calculation worksheet, I've entered two different formulas - one using the UDF to extract numbers from the original exported source data; and the other using an Excel native formula (an expanded version of the one kindly suggested by Fin Fang Foom) to extract numbers from the adjusted source data.

My thoughts are that an Excel native formula from the original exported source data would be best, however, I have no idea how to do it. Any suggestions? Alternatively, what would the VBA look like to convert the original exported source data to the adjusted source data layout and having done that, can anyone suggest any improvements to my Excel native formula that I've derived?

Many thanks in advance for any assistance.

Excel Video Tutorials / Excel Dashboards Reports

9. Senior Member
Join Date
29th July 2006
Posts
224

## Re: Alternative Solutions to SUMIF Custom Function/UDF Required

Originally Posted by VBA New Kid
My concern in writing macros to re-configure the data into a more useable format is the impact on the file size and also the time that it would take to execute. These concerns are from the end-user's perspective as the accounting firms have neither the time nor patience to wait for lengthy macros to process and the staff using the spreadsheet will typically be inexperienced staff so I need to make everything as simple as possible. Its also highly likely that they will replicate this file hundreds of times for their many clients so a large file size would be magnified many times and use of a lot of file server space. If a solution can be found to keep the file size down and to make the macro execution speedy, maybe this would result in a better layout and therefore avoid the need for UDF's???
Hopefully this gives you a better background on the problem and some of the stumbling blocks.
Ok so this file is not coming into your office but instead you need something to send out all over the place to re format these files?

Why is that a firm does not have the time to get things right this does not go with what you are saying? on one hand your data needs to be presented for many many customers but there is no time?

as above in my first post if you built a excel file that picks up all raw data from a folder / converts it and dumps it into a converted folder ready for use, i see as one of the best ways to deal with many updates as you say and to have it so that any office worker could use as the converter file would only have 1 button that says " Please update files" thats it they cant / should not go wrong.

you talk about making it speedy and this option would have to be one of the fastest and better than opening 100's of files and updating them 1 by one.

Excel Video Tutorials / Excel Dashboards Reports

10. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## Re: Alternative Solutions to SUMIF Custom Function/UDF Required

Hi,

Define acCode

='Original Exported Source Data'!\$A\$10:\$A\$18

In C1 on Calculation sheet,

=MATCH(B1&"*",acCode,0)

In C2,

=MATCH(B2&"*",acCode,0)

Define DataBase

='Original Exported Source Data'!\$B\$10:\$AA\$18

Define SumRange

=INDEX(DataBase,Calculation!\$C\$1,Calculation!\$B\$4*2-1):INDEX(DataBase,Calculation!\$C\$2,Calculation!\$B\$5*2-1)

In C10,

=SUMPRODUCT(--(MOD(COLUMN(SumRange)-MIN(COLUMN(SumRange))+0,2)=0)*SumRange)

HTH
Last edited by Krishnakumar; September 13th, 2006 at 13:54.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno