7 Replies Latest reply on Jul 7, 2014 2:11 PM by philmodjunk

# Sum (if) based on multiple conditions

### Title

Sum (if) based on multiple conditions

### Post

Hi Gents

I am trying to make a cash flow database for my business and I am new to use FM.

I have one table in which I am saving all purchases i.e.:

Unique ID, Invoice Number, Invoice Date, Supplier Name, Invoice Value, Type of Expense

I am also trying to make a yearly report where I can display a summary of all expenses in a table, i.e.: the column names are months and rows are types of expenses, like: Tools, Materials, Equipment. So in theory, I want to have 3 rows 12 columns each of data. Now I assume I need a summary field in my table which will do the calculation. I have been doing a little bit of Visual Basic in it's time years ago on a PC, so i tried to play with the functions and read help files but didn't get far.

If any of you kindly could explain what function I need to calculate how much I have spent for let's say Tools in January I would really appreciate. So a function is needed to look up all invoices with date containing January as month and Type Of Expense "Tools".

Now it would be great to have a drop down list with years somewhere aside so I view the spending for each year ;)

I know I am probably asking much.

Any help would be really appreciated.

Thank you

Nat x

• ###### 1. Re: Sum (if) based on multiple conditions

This is a commonly expressed request.

See these two threads for some ideas on how to do it:

Sum_Calculation based on condition

FMP 12 Tip: Summary Recaps (Portal Subtotals)

• ###### 2. Re: Sum (if) based on multiple conditions

This seems so complicated. Is there any other simple way to do it? I was under an impression you can use something like DCAOUNTA function in Excel. Point the table, and sum based on few conditions?

• ###### 3. Re: Sum (if) based on multiple conditions

The simplest solution for the new developer is to set up the needed relationships using match fields. Since a Sum function or a summary field defined in the related table both return aggregate values based on all related records, you can use the relationship to match to only those records you need for a total.

And if you only need to display such subtotals, the filtered portal option is even simpler.

• ###### 4. Re: Sum (if) based on multiple conditions

I'll give it a try and will post an update.

Thank you again PhilModJunk ;)

• ###### 5. Re: Sum (if) based on multiple conditions

Do you think I could send you my database, could you have a look into it?

• ###### 6. Re: Sum (if) based on multiple conditions

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 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

All I initially want to achieve is a TextBox on my Layout which will show the total of i.e all Invoices paid for Tools in February which in this case is \$350.

At later stage I would like to have this TextBox to be driven by a DropList where I can choose the month availiable in the Invoice Date column.

This is killing me :(

• ###### 7. Re: Sum (if) based on multiple conditions

assuming that Invoice date is properly a field of type date like it should be, add this calculation field to your table:

Invoice date - Day ( Invoice date ) + 1

Call it cMonth.

Define a date field, SelectedMonth in your layout's table with this auto-enter calculation:

self - Day ( self ) + 1

Clear the do not replace existing value check box.

Define this relationship, but using your Tutorial: What are Table Occurrences? names in place of mine:

LayoutTable::SelectedMonth = RelatedTable::cMonth

Define a calculation field in Layout table as: Sum ( RelatedTable::cMonth )

Put that field on your layout and you can get a total for any month/year date you select in the SelectedMonth table.

LayoutTable and RelatedTable can be two occurrences of the same table linked in a self join relationship.