Jump to content
Sign in to follow this  

SUM IF, Conditional SUM - please help!

Recommended Posts

sitekeepers    0

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

Share this post

Link to post
Share on other sites
comment    1,368

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.

Share this post

Link to post
Share on other sites
sitekeepers    0

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?

Share this post

Link to post
Share on other sites
Wim Decorte    446

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.

Share this post

Link to post
Share on other sites
comment    1,368
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.

  • Like 1

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this  

  • Similar Content

    • By crazybake
      There are three tables:
      Services:materialName::ServiceTransactionsLineItems:Material_FK and Services:MaterialYear::ServiceTransactionLineitems:TransactionLineItemYear
      (Services may change from year to year, and within one transaction you might line items for both current and future years.)

      :Services:MaterialBudgetAllocation is a user entered number.
      What I am hoping to do is: Get the yearly total amounts of each material (Material_FK) and subtract that from Services:MaterialBudgetAllocation to get a remaining amount.
    • By sal88
      Hi all
      I'm trying to find the Client that has the most Job time over the last 30 days, divided by the number of seats that they have. The tables are:
      Client_ID, Seats
      Client_IDF, Job_time, Job_Date
      The best I've managed so far is to find the distinct Client_IDF from all Jobs:
      ExecuteSQL ( "SELECT DISTINCT j.log_client_IDF FROM Jobs J WHERE j.Log_date >= ?"; ""; "";  $date_30_days_ago) And then loop through the list and perform the 'divide by seats' calculation for each line
      Or to do something similar but from the Clients table:
      ExecuteSQL ( "SELECT SUM (j.labour_units), c.Name FROM Clients C JOIN Jobs J ON j.log_client_IDF = c.Client_ID WHERE l.Log_date >= ?"; ""; ""; $date_30_days_ago) But no joy in either case when it comes to actually performing calculations within SQL.
      Is this possible in FM SQL?
      Many thanks
    • By Marinos Kangas
      I am new to FileMaker and still trying to find my way around the scripting language.  Even though the scripting language is quite rich, I don't feel as comfortable and flexible as I do in lets say VB.
      To my query now;  I have designed a solution for my company which processes invoices.  These invoice forms input numerical amounts tied on budget codes for each department and in turn are stored in a different table.  As you can see on the image attached, I cannot find a calculation function that will sum all the amounts for each BudgetCode into AmountAllocated.  AmountAssigned is the yearly department budget predefined.
      I will be grateful for any suggestions or ideas.

    • By dwdata
      Hey gang,

      Trying to figure something for a fellow lister. I am tring to run a query in FileMaker using ExecuteSQL() that works perfectly when I run a similar query in a normal SQL editor:

      SUM(i.Quantity) AS sQTY  

      FROM invoice_invoicelines_Product p  

      JOIN invoice_InvoiceLines i ON p.pk_ProductID = i.fk_ProductID AND i.fk_InvoiceID IN (1,2,3)  

      WHERE p.pk_ProductID IN (1,2,3)  

      GROUP BY p.pk_ProductID

      It produces a "?" (which is very informative).

      I guess need to know if this is too much for FileMaker's SQL engine or if FileMaker is going to force me to use something that is not tradition SQL protocol/syntax.
      Any feedback would be appreciated.
    • By Gurratheboy
      Hi Guys, I'm stuck to say I wonder if it has with brain power to do...
      Table "Pencils" fields:
      State                                                                                                      Text | Has one of two values: "Activated" or "Deactivated"
      Brand                                                                                                     Text |Can be one out of many brands......
      Pricetag                                                                                                  Numerical |the price of each pencil
      Sum_Pricetags_samebranded_and_activated pencils                           Calculation |?????
      This is what I can't figure out for the last calculation field in the table "Pencils", How do I make a calculation out of the following:
      In the calculation I want the Pencils from the same brand and with the same state "Activated" have their pricetags summed up and only if the two conditions are met.
      2 pencils are of same brand and they are both in the state "Activated". The one pencil have a pricetag of 18£ and the second one have a pricetag of 10£
      Because they are of same brand and both have the same state, I want their price to sum up, 18+10=28£ 
       It does not matter what the field say if the conditions are not met for other records, the field then can stay blank.

Important Information

By using this site, you agree to our Terms of Use.