COUNT total rows and use value in formula

Platform discussions
1

I’m trying to figure out a way to return the amount of rows based on certain conditions and then use that value within a formula column. I’m not sure if this is possible, I’m very new to Monday.

For example, I’d like to count the amount of rows that have a date entered in one of the columns in the board and have a particular status column value. If date is empty, I’d then want to use that row total value within a formula.

We’re building out a Sales Forecasting model and need to be able to figure out success rate. Success rate is defined by (Total Successes / (Total Success + Total Fails)) * 100.

I’ve got the formula written out below, but having trouble figuring out how to pull the total count of rows into that formula. The bolded items below are where I’m having trouble figuring it out.

Currently, my formula is as follows:

MULTIPLY(DIVIDE(COUNT rows WHERE AND({POC Start Date} <>“”, {Current Stage} = ‘1-Won/Active’),
(SUM(COUNT rows WHERE AND({POC Start Date} <>“”, {Current Stage} = ‘1-Won/Active’), COUNT rows WHERE AND({POC Start Date} <>“”, {Current Stage} = ‘11-Deal Dead’)))), 100)

2

I would say monday.com just fundamentally doesn’t work in this way. One of the key challenges people face when they start using monday.com is expecting it to behave like a spreadsheet, when really it’s more like a database. Data is consistent across all rows, unlike a spreadsheet where there is more context and relevance to a row’s position.

This can be achieved in monday.com by aggregating data. I would approach this by creating a second board and connecting all the items that you’d want to include in this calculation.

Then I’d create some formula columns on the main database to be something like:

“IsSuccess”

IF(
    AND(
        NOT({POC Start Date}=""),
        {Current Stage}="1-Won/Active"
    ),
    1,
    0
)

“IsClosed”

IF(
    AND(
        NOT({POC Start Date}=""),
        OR(
            {Current Stage}="1-Won/Active",
            11-Deal Dead"
        )
    ),
    1,
    0
)

Then have all these items linked (can be one way) from the second board you created. Make sure that that the mirror columns are set to sum the mirrored values.

Then on the new board, you can just create a simple formula column to divide these totals and multiply by 100.

3

Hello Ryan,
You can easily do his by autoimporting your board to a spreadsheet with the Smart Spreadsheet app and using the IF formula.

  1. Create a ‘Helper’ column: =IF({column cell with a date} = “date”, 1, 0)
  2. Then Sum this column.
    I hope this can help.
4

Hi Ryan,

This can be easily done with a 3rd-party app called the Advanced Formula Booster. It is designed to revolutionize formulas and automations in monday. Among many unique features, it allows you to access any item from the same board or any other board.

In this case, you would use the following syntax:

2024-07-10_08-37-09
2024-07-10_08-37-091074×117 57.5 KB

  • In the first row (the app’s syntax editor can have up to 100 lines), you retrieve all the start dates of the board and store it in a list called [AllDates].
  • In the second row, you keep only the ones that are empty.
  • In the third row, you retrieve the number of items.

As you can see in the screenshot, the app allows you to run a simulation to immediately show you the results (without affecting your board data). So you can see in the first line, that the line is made of 5 values (the list separator is |) among which 2 are empty. They are removed by the filter and the final count is 3.
Any text in-between is a variable, i.e. the value is stored and can be called later in the formula as with the [AllDates] variable calculated in row 1 and used in row 2.

The app can write any calculation to any type of column in your board. So in your case, instead of having a formula column, you would create a numbers column called Success Rate,. That means that this rate could be used anywhere in widgets, automations, etc. without the limitations of the formula column.

Hope it helps.