# ConditionalSummary ( TypeField ; Type ; SumField )

Sum a SumField for records where TypeField matches

Average rating: 4.5 (33 votes) Log in to vote

 Koji Takeuchi - Show more from this author TonicNote, Inc. https://tonicnote.com

Sample input:
ConditionalSummary ( cat ; "a" ; num )
Sample output:
30

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

ConditionalSummary ( TypeField ; Type ; SumField )
2011.07.13, Koji Takeuchi

description:
Sum a SumField for records where TypeField matches "Type" param.
Original concept by TypeSumField ( TypeField ; Type ; SumField ; Counter ) function by Bruce Robertson , thanks Bruce and David.
This function is "no Counter param" version.
2012.03.13 Updated to solving a bug spotted out by Bruce Robertson. Thanks, Bruce.
ex1:
FoundCount = 3
record 1: cat = "a", num = 10
record 2: cat = "b", num = 5
record 3: cat = "a", num = 20
ConditionalSummary ( cat ; "a" ; num )
-> 30
ex2:
List ( child::cat ) = "a¶b¶a"
List ( child::num ) - "10¶5¶20"
ConditionalSummary ( child::cat ; "a" ; child::num )
-> 30

## Comments

 Guillermo Dewey, Monterrey, MexicoJan 18, 2012 After hours and hours spent trying to Sum() in with a condition and not being able to acomplish the task, this function made my day :) thanks a lot for sharing!! For some reason I was not able to make the Bruce Robertson's function.
 Bruce RobertsonMar 4, 2012 This function will not return accurate results if there are "holes" in the data. (Empty values in the sum field). That's why I used the counter in my function; and why I required supplying the found count or related count so that you count down from the max found or related record. There may be ways to write it without the counter but I don't think this function works as written.
 Koji Takeuchi, TonicNote, Inc.Mar 12, 2012 Thanks for your comment, Bruce. That's true. You really hit the spot! I revised this function. It's terribly complex but I think this version works fine...
 Lisa, FrankfurtMar 16, 2013 I insert the function in a calculationfield, place the field on my total summary layout part and it always shows all results not the results from foundset. I check in the calculationsettings "not stored" and it shows only the value of one record in the foundset. ??????? Please help. Lisas4@hotmail.com
 Koji Takeuchi, TonicNote, Inc.Mar 28, 2013 Hi Lisa, Please make sure the calc field is "unstored".
 Jay Gui, SingaporeApr 18, 2013 Hi, I tried using the Custom Function above, but I cant seen to get it to work.. As when I click ok: I get this Error at this part of the code "An operator (e.g. +,-,*,...) is expected here" Case ( \$\$ConditionalSummary_counter > 1 ; val + ConditionalSummary ( TypeField ; Type ; SumField ) ; Let( \$\$ConditionalSummary_counter = "" ; val ) ) ) Please advise what I can do, as this is a very good thing to have in reporting function.
 Ignacio, SydneyMay 23, 2013 Works flawlessly for me. Thanks Koji.
 Michael, USAMay 29, 2014 Is it possible to modify this two have two conditions?
 Koji Takeuchi, TonicNote, Inc.May 29, 2014 Michael, You can do it. 1. Add TypeField2 param to this function and recursive call part in last "Case". 2. Modify "val" variable as following. org: val = Case ( Type = "Total" or Type = GetNthRecordã€€( TypeField ; \$\$ConditionalSummary_counter ) ; GetNthRecordã€€( SumField ; \$\$ConditionalSummary_counter ) ; 0 ) OR version: val = Case ( Type = "Total" or Type = GetNthRecordã€€( TypeField ; \$\$ConditionalSummary_counter ) or Type = GetNthRecordã€€( TypeField2 ; \$\$ConditionalSummary_counter ) ; GetNthRecordã€€( SumField ; \$\$ConditionalSummary_counter ) ; 0 ) AND version: val = Case ( Type = "Total" or ( Type = GetNthRecordã€€( TypeField ; \$\$ConditionalSummary_counter ) and Type = GetNthRecordã€€( TypeField2 ; \$\$ConditionalSummary_counter ) ) ; GetNthRecordã€€( SumField ; \$\$ConditionalSummary_counter ) ; 0 )
 Sam, san joseJul 8, 2014 Hello Koji, For using the two variables I am not sure about 'recursive call part in last Case'. Could you please post a custom function and post it here. Thank you
 Renee, NapervilleApr 8, 2015 Hi, I'm working with this function and it almost does what I need it to. It actually does what I want, but gives me a pop up saying that the "specified field cannot be found". The reason it says this is because, for the last last parameter, instead of inserting a field, I insert Count(field). Like I said, it gives me the right results in the viewer/watcher, but gives me that popup error message first. I don't know enough about Let statements to be able to modify it for myself. Basically if field A = cat, I need to count field B. So Something like ConditionalCounter ( Field A; "cat"; Field B). Can you help?
 Aleksandar, MacedoniaJan 29, 2016 Thank you. A life saver

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

### Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 20 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: