Left(Estimates_Inhouse::Billing Category;4), since it is defined in jobs, only refers to the "first" related estimates records. If it starts with those letters, the Sum is computed. Since Sum(Estimates_Inhouse::total) computes the total of all related estimates records, you get the total you are seeing when the first record's category field contains text starting with 1_DF.
You'll need to use one of two approaches here:
Define this calculation in Estimates, in a calculation field, c1_DF Total:
IF ( Left(Estimates_Inhouse::Billing Category;4)="1_DF"; Total);"")
Then Sum(Estimates_Inhouse::c1_DF Total) will compute the correct total. This does indeed require a separate calculation field in Estimates for each such category where you want a total.
Option 2 (Requires FileMaker 11 and can require extra window refreshes)
Define a summary field as the Total of your total field.
Place this summary field on your layout inside its own one row portal that refers to the same table occurrence in show records from as your first portal.
Define a portal filter expression: Left(Estimates_Inhouse::Billing Category;4)="1_DF"
The advantage here, is that you can make the same "Grand Total" compute and display totals for different categories simply by changing the value of a global field referenced in the filter expression or you can put several copies of this portal and summary field on your layout, but with different filter expressions to get different category based totals.
Thanks very much - that makes perfect sense. I created my SUM in the Estimates table based on the condition (value is zero if condition is not met) and then on my Jobs table i added a SUM of that field.
Haven't made the jump to 11, so I think this will do - thanks very much.