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

# Monarch version of Excel CountIf function

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

• ###### Monarch version of Excel CountIf function

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=&quot;Test&quot;,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

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

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

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 (=,&lt;,&gt;,&lt;=,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?