SUM IF, Conditional SUM - please help!

[sitekeepers]

This is doing my headache now..

When paying £££ for FM and reading all this stuff on FM website 'how simple to use it is' I was under impression I'll have this pretty much sorted out in no time, especially that I am not trying to design anything fancy here, just stuff which I was able to do in Excel..

All I want to do is this:

My table (GeneralLedgerTable) have i.e the following Fields:

Unique ID     Invoice Date     Total Value    Supplier Name    Expense Type

00001            1 Jan 2013         $100             xxx                      Materials

00002            15 Feb 2013       $150             yyyy                     Tools

00003            18 Feb 2013       $200             zzzzz                    Tools

00004             2 Mar 2013        $100             aaaa                     Tools

 

What I am trying to achieve is to have a separate Layout which displays the cash flow; for the purpose of this exercise the new layout would have (for start) just one simple Text Box which will calculate the total of Invoice Values in i.e February which match the condition of Tools.

In excel I would use DCOUNTA - job done. I thought this will be as easy (or easier) with FM. 

My weeks research shows that there is a lack of this feature in FM for a very long time, a lot of people has this problem, new features in FM13 don't cover this subject and the only resources I found on the web were Custom Functions which didn't apply to my issue as they could not read Dates (Date Ranges) just separate conditions (formatted as text).

 

I know I am probably going one step ahead now but I also would like to have a Drop List where I can choose a year and my initial Text Box would then still show values for February with matching condition for Tools but for another Year.

Again in Excel I would use simple Visual Basic function with the combination of the function DCOUNTA on the spreadsheet.

I understand I may think in VB terms all the time, but 'it was meant to be simpler' as the say on the FM home page.

 

This is killing me

Any help would be greatly appreciated

Nicole x

[comment]

There are several ways to do this in Filemaker, depending on exactly what you want, where you want it and - most importantly - why you want it.

 

The simplest way would probably be to find the records of interest (in your example, I believe that would be records whose Invoice Date is in February 2013 AND Expense Type = "Tools".  Then use a summary field, defined as Total of [Total Value] to show the summary value you are after.

 

--

P.S. This is a professional forum, and I am not sure the picture you have chosen as your avatar is appropriate here.

[sitekeepers]

Hi Comment

Thank you for your reply.

 

This is exactly what I have start doing when I first approached this issue - I have used IF function to do it but this is causing even more problems because:

 

If I have 12 months and 4 different Expense Types to Sum (i.e Tools, Materials, Wages and Other) I need additional 4 columns to summarise each Expense Type now multiply that by 12 as there is one needed for each month. The Table started to grooooow. Now this is just for one Year.

 

Question is if I would like to later add another Expense Type, that means a massive redesign to the Table that sums it all.

 

How to resolve this issue?

[Wim Decorte]

Do a search on "cross tab" reports.

 

Expense types are their own entity in their own table so you should not have to add fields to your main table to summarize each type.

[comment]

If I have 12 months and 4 different Expense Types to Sum (i.e Tools, Materials, Wages and Other) ...

 

The standard Filemaker model for this type of situation is to produce a vertical report with sub-summaries, for example:

 

January 2013

• Tools: $120

• Materials: $240

• Wages: $75

• Other: $94

SubTotal: $529

 

February 2013

• Tools: $140

• Materials: $210

• Wages: $45

• Other: $98

SubTotal: $493

...

GrandTotal: ...

 

This requires only one summary field that returns different values, based on the part in which it is located. In the above example, there are two sub-summary parts and one grand summary part. Adding months and/or types to the data to be summarized does not require any changes to the file's schema.