6 Replies Latest reply on Sep 16, 2015 5:19 PM by EdwinMerced

    Countif Unique values across multiple fields

    EdwinMerced

      I have 10 multiple fields (named A1 through A10). These fields only accept values 1, 2 3 or 4 from a Value List. I need to count the amounts of 1s, 2s, 3s, and 4s across all fields (A1-A10). For example: 4-1s, 2-2s, 2-3s and 2-4s

       

      I have used =COUNTIF in Excel to do this but do not see that function in Fielmaker.

       

      Thanks for any help.

        • 1. Re: Countif Unique values across multiple fields
          erolst

          EdwinMerced wrote:

          I need to count the amounts of 1s, 2s, 3s, and 4s across all fields (A1-A10). For example: 4-1s, 2-2s, 2-3s and 2-4s

          Let (

          theList = List ( A1 ; A2 ; A3 … A10 ) ;

          List (

             ValueCount ( FilterValues ( theList ; 1 ) ) & " 1s" ;

             ValueCount ( FilterValues ( theList ; 2 ) ) & " 2s" ;

             ValueCount ( FilterValues ( theList ; 3 ) ) & " 3s" ;

             ValueCount ( FilterValues ( theList ; 4 ) ) & " 4s"

            )

          )


          though you very likely should use a child “A” table with 10 records per parent, rather than 10 fields.

          • 2. Re: Countif Unique values across multiple fields
            EdwinMerced

            Hello and thank you for your reply:

             

            I used the following in a calculation field I called COUNTIF_FOR_A:

             

            Let (theList = List ( A1;A2;A3;A4;A5;A6;A7;A8;A9;A10;A11;A12;A13;A14;A15;A16;A17;A18;A19;A20;A21;A22;A23;A24;A25;A26;A27;A28;A29 );

            List (

               ValueCount ( FilterValues ( theList ; 1 ) ) & "1" ;

               ValueCount ( FilterValues ( theList ; 2 ) ) & "2" ;

               ValueCount ( FilterValues ( theList ; 3 ) ) & "3" ;

               ValueCount ( FilterValues ( theList ; 4 ) ) & "4"

            )

            )

             

            But when I place the field in the form it returns a strange number (9110210304). I need it to tell me how many "1" were chosen, how many "2" where chose, how many "3" and how many "4" where chosen.

            • 3. Re: Countif Unique values across multiple fields
              erolst

              EdwinMerced wrote:

              it returns a strange number (9110210304).

              1. That's not possible, because List() will insert CRs; so the actual result should be:

               

              91

              102

              103

              04

               

              2. if you had used (as suggested)

               

              ValueCount ( FilterValues ( theList ; 1 ) ) & "x 1"

               

              you would get

               

              9x 1

              10x 2

              10x 3

              0x 4

               

              which the result you're after.

               

              Alternatively, use e.g.


              "1: " & ValueCount ( FilterValues ( theList ; 1 ) )


              which gives you

               

              1: 9

              2: 10

              3: 10

              4 : 0

               

              Please consider my suggestion regarding the use of a related table; your current non-normalized structure will cause (more) issues down the road.

              • 4. Re: Countif Unique values across multiple fields
                EdwinMerced

                Thank you elrost.

                 

                I am new to FM. How do individually extract those four numbers? (as I want to calculate percentages for each one).

                • 5. Re: Countif Unique values across multiple fields
                  erolst

                  EdwinMerced wrote:

                  How do individually extract those four numbers? (as I want to calculate percentages for each one).

                   

                  Well, it would really be better to use a related table and a sorted set, because the aggregate field type (summary fields) allow you to analyse (and do other things with) a set of records much more conveniently than with a group of fields.


                  EdwinMerced wrote:

                  I am new to FM.


                  So wouldn't it be a good idea to read the Help or other FM documentation, or attend a training? Learning about data normalization and creating reports and summaries would be helpful.


                  That being said: you could create two number fields, each with 4 repetitions, as

                   

                  field: cNumberCounter =

                   

                  Let ( [

                  i = Get (CalculationRepetitionNumber) ;

                  theList = List (Extend(A1);Extend(A2);Extend(A3);Extend(A4);Extend(A5);Extend(A6);Extend(A7);Extend(A8);Extend(A9);Extend(A10);Extend(A11);Extend(A12);Extend(A13) // etc. )

                  ] ;

                    ValueCount ( FilterValues ( theList ; i ) )

                  )

                   

                  and


                  field: cPercentages =

                   

                  Let ( [

                  i = Get (CalculationRepetitionNumber) ;

                  all = Count (Extend(A1);Extend(A2);Extend(A3);Extend(A4);Extend(A5);Extend(A6);Extend(A7);Extend(A8);Extend(A9);Extend(A10);Extend(A11);Extend(A12);Extend(A13) // etc. )

                    ] ;

                  cNumberCounter[i] / all * 100

                  )

                   

                  Be aware that the second field references the first one.

                   

                  Easiest to use text objects as labels to show what these values stand for.

                  • 6. Re: Countif Unique values across multiple fields
                    EdwinMerced

                    Thank you for your suggestion elrost. I have already begun taking some virtual training and reading on Filemaker.

                     

                    Also, thank you for your solution.