ifsum ( string ; filed ; fieldsum )

compared to "ifsum" in Excel

Average rating: 5.0 (1 vote) Log in to vote

Christian Bosshard - Show more from this author
CBC Immobilien & Treuhand GmbH
https://www.cbcit.ch

Sample input:

ifsum ("A" ; letters ; value )

Sample output:

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

/*
When the value of<< field >>in a related table is = string, then the value of <<fieldsum>> (number) will be added to the sum. filedsum cannot be empty! it has to have zero or another number as a value. if not, the added sum will not  be correct or the result is false
*/

Solange (
 
[
  $txt.list = Liste (field) ;
  $zhl.list = Liste ( fieldsum) ;
  $txt = string ;
  $z = 0 ; 
  $n = ElementeAnzahl ( Liste ( fieldsum ) ) ;
  $sum = 0 
] ;

$z ≤ $n ; 

[ 
  $z = $z + 1 ; 

  $sum = $sum + Wenn ( HoleWert ( $txt.list ; $z) = $txt ; HoleWert ( $zhl.list ; $z ) ; 0 )

] ;

$sum

)

Related Table with 4 Records:

Record 1 : letters = "A", value = 2
Record 2 : letters = "C", value = 3
Record 3 : letters = "B", value = 4
Record 4 : letters = "A", value = 5

ifsum ( "A" ; letters ; value )
Output = 7 ( letters ="A" has 2 records, one with value=2 and another with value = 5)

Comments

		Lazarus Sismanis, Decision Group Nov 11, 2022
Thank you Christian for this useful custom function. For the English speaking users that might not recognize the German language functions of 'Solange', 'ElementeAnzahl' and 'HoleWert', here is a version with English named functions: --------------------------------------------------- /* When the value of<< field >>in a related table is = string, then the value of <> (number) will be added to the sum. filedsum cannot be empty! it has to have zero or another number as a value. if not, the added sum will not be correct or the result is false */ While ( [ $txt.list = List (field) ; $zhl.list = List ( fieldsum) ; $txt = string ; $z = 0 ; $n = ValueCount( List ( fieldsum ) ) ; $sum = 0 ] ; $z â‰¤ $n ; [ $z = $z + 1 ; $sum = $sum + If ( GetValue ( $txt.list ; $z) = $txt ; GetValue ( $zhl.list ; $z ) ; 0 ) ] ; $sum ) ---------------------------------------------------

		Christian Bosshard, CBC Immobilien & Treuhand GmbH Nov 11, 2022
Thanks for translation :-)

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

ifsum ( string ; filed ; fieldsum )

Comments

Support this website.

Support this website.