4 Replies Latest reply: May 15, 2014 9:56 AM by Data Kruncher RSS

    Monarch version of Excel CountIf function

    jrzivnuska _

      How would I create in Monarch the calculated field function equivalent to Excel's CountIf?

        • Monarch version of Excel CountIf function
          Data Kruncher

          I have a solution in mind for this, but it's probably not the most elegant thing ever devised. Should work, though.

           

          Create a calculated numeric field named CountIf (creative, no?). Assuming that you want to count how many times the MyField field contains "Test", write the following formula:

          [font="courier"]If(MyField="Test",1,0)  /font[/quote]Now create a summary with MyField as the key field, and Countif as the measure field.

           

          That should tell you how many occurences of "Test" appear in your data, functionally equivalent to Excel's CountIf function.

           

          HTH,

          Kruncher

          • Monarch version of Excel CountIf function
            jrzivnuska _

            Will try tonight.  Many thanks for the old 1-2 step.  I was locked into thinking of a single step, which limitation you helped me overcome.

            • Monarch version of Excel CountIf function
              Nick Osdale-Popa

              How about this:

              Create a runtime parameter: I used MyParam

              Create a Filter: set it to MyField=MyParam, I named it MyFilter

              Create A summary:

              Apply Filter: MyFilter

              Do not insert a Total Line.

              Key Fields: MyField

              Measure: Count

               

              This will give you the ability to count whatever text you are looking for with the runtime parameter.

              • Monarch version of Excel CountIf function
                Data Kruncher

                A runtime parameter adds a lot of flexibility. I need to remember that anytime a formula contains "If(TableField="constant"...) that's an opportunity to optimize with a runtime parameter.

                 

                But why stop there? By adding another runtime parameter for the operator (=,<,>,<=,etc.), and nested ifs along with some conversion functions, you could probably handle just about any operator and any data type for the TableField for a truly flexible CountIf.

                 

                You up to a Proof of Concept Nick? How about a SumIf while you're at it?