Countif Formula

[NatashaMann]

Hi,

I have some data similar to below:

Jan-22 | Feb-22 | Mar-22 | Apr-22 | May-22 | Jun-22 | Jul-22 |
30.1 | 22.5 | 22.5 | 0 | 40.2 | 33 | 22.5 |
11.5 | 32 | 11.5 | 68.2 | 27 | 39.4 | 72.3 |
46.2 | 46.2 | 46.2 | 46.2 | 46.2 | 46.2 | 46.2 |
15.7 | 13.4 | 0 | 11.5 | 11.5 | 11.5 | 11.5 |

And I basically want to do a COUNTIF formula so that a new column is created to count the number of times a certain months value occurs in that particular row.

So for example I would want a column that counts the number of times that the Jul-22 value occurs in the past six months. So the new column would count that for the first row 22.5 occurred twice from Jan-22 to Jun-22, in the second row there would be a count of zero as 72.3 did not occur within this row, in the third row 46.2 occurred 6 times, and finally 11.5 occurred 3 times in row 4.

I don’t however want the new column to count the number of times a certain months value occurs within the table as a whole. For example 11.5 occurs a total of 5 times within the other rows, so I don’t want the new column to have a count of 5 for row 4.

Hope this makes sense any help would be extremely appreciated

[ArjenEX]

Hi @NatashaMann

Welcome to the KNIME community!

Would something like this be workable for you? This would a way to approach it.

I assumed that you manually want to select the month to analyze where the available options should be determined dynamically. The Column Selection Configuration node is an option to arrange this. As such, the component is mainly catered to handle this. It:

First step is to extract the column headers, as they contain the selection values.

For the Column Selection Configuration to work properly, the list of options needs to be passed along as a variable of type string(list). As such, the months are converted with the transpose node first, followed by a groupBy node that creates a list.

Last step is to convert it to a variable with the Table Row to Variable node.

This is now fed into the Column Selection Configuration. To use it, navigate to the Flow Variables tab and assign the just created flow variable to the possibleColumns line.

This will now populate the component wizard with the available months.

If you select a month and click apply, the configuration will automatically also store this value into a flow variable. Imagine it being the input and output of the configuration: the whole list goes in, only one month comes out.

Simultaneously, I create a combined string of all values with the Column Aggregator node which makes it easier to perform occurrence counting later on.

Next step is a Column Expression. Both the concatenated string and the selected month from the configuration are passed along to the node. Here, I count how many times the value of the selected mount is present in the concatenated string by using:

count(column("Concatenate"),column(variable("column-selection"))) -1

Here, the -1 offsets the total count to cater for your desire you exclude the instance of the selected month as well.

After a small clean-up, this is the final result for July:

For reference, the results for June:

See the WF:
CountIf Formula.knwf (47.2 KB)

Hope this provides some inspiration!

[NatashaMann]

This couldn’t have worked better - thank you so much! And thank you for providing a solution in such detail, it really helped

[ArjenEX]

Glad to hear! Please mark it as solution if it helped you so that fellow KNIME’rs can also benefit from this in the future.