Sum field where another field = x
I'm an intermediate level user, with Filemaker Pro 11, running on Windows Vista.
I'm trying to create a calculated field, which is the sum of instances in another field where a third field equals a defined value. It's in a Budget table (named Budget), where I've got different 'pots' of money that I need to keep track of, but I want to sum it all up so I can tell how much I've got left.
I've got a value list for Categories, with values Category1, Category2, up to Category 4. These are the different pots of money. Every time I assign a cost to a task, I allocate it to the appropriate Category. This is stored in a field called Budget_Location.
I also keep track of what the Estimate is, versus the Actual cost once we get an invoice.
I'd like the summary field (which should total the Estimated Spend in a given Budget Location) to be something like this, but my programming language is failing me (I'm defaulting to Basic!):
Estimate_Category1 = sum (Budget::Estimate) where Budget_Location = "Category1"
I've tried an IF expression, but it returned nothing at all. Any help would be much appreciated!