3 Replies Latest reply on Mar 4, 2014 3:18 AM by Kaorin

# Calculation of Total under specific criteria per month

### Title

Calculation of Total under specific criteria per month

### Post

Hello,

I wonder someone can let me know how to do the calculation of following "without" using summary fields / part:

I'd like to get the TOTAL amount spent each month under Calculation field, and so far I have

- AMOUNT SPENT field (number)       and

- MONTH (text) field  , Jan / Feb / Mar ...... Dec

Is there anyway of using a calculation field and set criteria / function / formula so that I don't have to keep "sorting" to get the instant Amount Spent on each month under one Table?

Say I'm trying to get the Total Amount Spent for March without using Summary fields & part. in a different Table as an Annual Report.

It's ironic - I'm trying to set the Brows Mode of my Filemaker to look like Excel spreadsheet on this occasion! Please don't tell me to go back using Spreadsheet.

I use FM 12 for Mac.

Thank you.

• ###### 1. Re: Calculation of Total under specific criteria per month

And what happens if you have data for Jan 2014 and data for Jan 2015? Won't those be two separate totals?

are the amount and month fields the only fields in this table? If not, how is that table set up?

And how do you want this to look on your screen?

I'm not as yet convinced that you should do this without summary fields as you can use a single summary field to show multiple monthly sub totals on the same report.

• ###### 2. Re: Calculation of Total under specific criteria per month

Can't you set up a relationship based on dates ? So assume your current table is called expenditure and has two fields called expenditure_date and expenditure_amount . Have a second table called total expenditure with three fields called start of month  , end of month and total expenditure . The relationship could be

expenditure:: expenditure date >= total expenditure :: start of month AND

expenditure::expenditure date <= total expenditure :: end of month

Then total expenditure::total expenditure = sum(expenditure amount).   Thanks kaps

• ###### 3. Re: Calculation of Total under specific criteria per month

Yes, that works! - Thank you, Kaps.