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.