Hey gang! I’m building out a CRM that tracks all my leads. They are on my “Buyers” board. For each lead they have a $$$ amount associated with their loan amount. I have referrals partners that connect me with the leads. Those are tracked on my “Partners” board. On my “Partners” board, is there a way to sum up the volume they refer and a way to count the number of leads they send from data on the “Buyers” board?
Yes, depending on how Buyers are created there is also a way to automate this.
But the setup I‘d go for in your scenario is as follows:
-
Have a connect Boards column in the Partners Board that connects Partners to Buyers
-
When a Buyer is created and was submitted by a Partner, the Buyer should be linked to that Partner
-
Now in the Partners Board you can simply mirror the $$ Amt from the Buyers Connect Board Column - it will summarize it from all connected Buyers
-
You can see the count of referred Buyers in the Connect Board column. Alternately if you want this in a more visible way: create a formula column in the Partners Board and reference the Buyers connect Boards column - you can select to display the count directly this way.
Thanks! I expected it to behave more like a SUMIF formula from Excel. On the Buyer’s board, I have the real estate agent listed in one of columns. If “Nick S” is the agent on 3 deals, is there way to sum or count the number of deals next to Nick S on the Partner board? There has to be something easier than assigning each buyer to the agent.
Hi Nick,
You may want to consider the Advanced Formula Booster, a 3rd-party app that allows you to create formulas that go far beyond whatever is available in monday or other apps.
Here is how I would handle this case.
You have a Buyers board. I assumed your Partner information is stored in a dropdown column.
You have a Partners board:
Here is the formula that would give you what you want:
In the left pane (1), the syntax. In the right pane (2), the simulation for Nick S (You can simulate formulas directly within the app, which allows you to test without affecting your board data).
Here is the formula’s full explanation:
- Line 2 gets the name of the Partner from the Referred By column (we use the LABELNAME function because the partner’s name is a Label, not just plain text).
- Line 4 gets all the values of that same Referred By column for all items in the board (the board having 3 items, we get 3 names, separated by | which is the list separator). See how we prefix the {Referred By} column with ‘BoardItems’. This is what gets us the list of all values in the board, instead of only the current item’s value.
- Line 6 filters that list and keeps only the positions of the Referral Partner. There are 3 names, in the list, the 1st and 3rd are ‘Nick S’, so we get a list of 2 positions: 1|3
- Line 8 counts the number of positions in the list. There are 2, that’s what we want to appear in the Referred Deals column of the Partners board.
- Line 10: {BoardItems.Amount} would get us all the amounts of the board (same principle used in Line 4). But this time, we use a position indicator to limit the items we want to take into account. A typical position indicator is #1: it gets you only the value of the 1st item. Here we use #[ReferrerPositions] which gives us items #1 and #3 which we sum up using the SUM function.
We’ve calculated the 2 values we need to write in the other board.
A formula can be linked to 2 boards other than the current board. So we need to link it to the Partners board. Once done, the Partners board is accessed by using the PartnersItems prefix.
- Line 12 gets us the partner’s position in the Partners board.
- Now that we know which position occupies our partner in the Partners board, we can write the calculated count and total amount by targeting the appropriate columns followed by a position indicator. In this case, the position indicator instructs the app to only update the specified row.
Automations necessary to run the formula automatically
All we have to do is create 2 automations in the Buyers board.
- When Referred By changes, Run the formula
- When Amount changes, Run the formula
I know this is advanced stuff, I know you may not want to use a 3rd-party app, but this is an interesting use case, so I thought I’d explain how this could be done in details.
Any question or help needed, let me know.
1 Like
There are several options here - though I believe connect boards column is your best way to go. (if you don’t want to use third party app hacks)
If you already have the agent listed in the Buyers Board (I assume in a text column) you could create a Dashboard view and add a chart widget to display the number of buyers per Partner. (This would not display in the Partners Board but it would solve the business requirement)
Alternatively, you could still use the connect Boards column. I assume why you are hesitant to use it, is because you already have a bunch of buyers who you don’t want to manually assign now.
This is in my opinion the most sophisticated way of doing this):
Simply add a Match Automation (When column changes and Partner Name matches Name in the Partners Board connect items)
You can choose a status column or any other column as a trigger for the change.
After doing that - change the column value on all existing Buyers, this will automatically assign all buyers to their respective Partners in the Partners Board.
Going forward you can the assign the Buyers when creating them (instead of manually typing in the Partner name you can use the connect Boards column search function to assign a buyer).
You put some work into this reply! I really appreciate it. I’ll digest this and see but this is exactly the use case I need.
1 Like
Hello @nick.steinhauer,
I am part of the Decadis team and our app sumUp for monday features a widget that can group items and sum numeric columns based on the users from a People column. I believe that this widget, namely the Group by widget, could help you to address this requirement in a Dashboard.
If you need more information about it, please, do not hesitate to contact us!
Best regards,
Vicente