Summary/Count of status

I have a spreadsheet that counts potential customers closings (we sell real estate), anticipated month to close, their transaction status and which quarter.

The google sheet looks like this

Screen Shot 2020-11-28 at 10.44.18 PM

The sheet formula for the top portion is “=countif(KLJStatusQ4,B1)” which effectively counts the occurence based on the transaction status.

  • How do I do this in Coda?
  • How to I use an interactive filter such as agent name and quarter so it summarizes based on the data I am filtering on?

Hi @Manager_TPJ and welcome in the community! :smiley:

You can definitely achieve the same results on coda!
You have 2 ways of doing that.

The first one is using Inline formulas (or canvas formulas), you just type “=” in a new row in a page of the doc and you can write a formula here.
The formula will be something like that: (PotentialBuyers(name of the table that contains the data).filter(Status=Closed).count() )
It can be translated as “From this table (the potentialbuyers with the status) take only the row that as “Status” have the text string “Closed”, and then count those for me”, the output will be a number.
You can then create other inline formulas to count the likely and so on

The second one, is to create a table, with columns named as the data you want to get (Uncertain, maybe, likely ecc) and the rows will be your agents names
Like this you can create more advanced filter that looks also who is the guy who have managed that sell or whatever, you have just to add “AND agent-of-this-client(a column in the original table)=current-loggedin-agent(a formula that shows the data only to the person who in in charge, alternatively this can link to a dropdown selector of all your agents and you can manually chose which one to see)”

The first approach is simpler and faster, but the second gives you more data that you can filter and analyze, and freedom of creating a multi user doc.
This means that from that table it’s a brief to get monthly or annual reports of agents productivity, or to display those just to them, or other 100 type of datas and analysis

This obv can be taken way further from this point but i’ve already wrote a super long and boring message so i’ll wait some inputs from you!

To close, if you can share your doc i can build something to give you some inspiration :slight_smile:

1 Like

Mario, you are the best! Thanks so much for giving me a few things to think about.

It may be better that I give you the whole picture… here’s a screen recording of the challenge…

Converting Google Sheets to Coda - Watch Video

1 Like

Did you end up getting this figured out @Manager_TPJ?

If you want to add me to your doc I can get you up and running real quick (with summary, etc) and I can also show you some important tricks to make sure you follow best practices that will help you in the future.

If you’re really only 4 days into learning Coda, then you’re clearly moving quick! Well done!

Hey @Manager_TPJ :smiley:

I’m super happy to hear your enthusiasm :smile:
From your video i do not see any particular possible difficulties, it’s just a matter of learning how to use filters and play with the data you get back!

The best part of coda (don’t know if you are ready for this :sweat_smile:) is that there is no “Coda way” but instead many “Coda ways” :joy: and many of them are not better or worst than the others (ok some can be super slow or inefficient but let start easy), it really depends on your data and how those data grow in time and ultimately how you want to display and analyze them later, so i’m not gonna suggest you a way, but more than one :smile:

First of all, you need to fix that filter with the count in that table, and for doing that the easiest thing would be to see your formula, better directly in the doc so i/we could see also the rest of the doc (if you are willing to share it, maybe without personal data in it, it would be awesome to get not only my opinion but also one from others :slight_smile: )

[p.s. a way could be to put in the table “forecast summary”, in the column “count”, a formula that says " count all clients that are present in the main table that have status=thisrow.status" (or, in formula language something like [ maintable.filter(status=thisrow.status).count ]) or you can try also with a switch formula, but first approach it’s cleaner and easier :slight_smile: ]

Then, the dropdown you showed, the one that shows your agent, is that doc just for you or you want to open it also to others? because if it is also for others you can start developing it as a multiuser doc, and this part is incredibly interesting but also a little bit “hardy” to get, but we are here to help!

Then, which type of analytics you want to get from your data’s? like “how many property have xyz sold this semester” is an easy task, or also “how many clients we have” or “how many clients “very likely” we have this semester instead of the previous one”, in this i’ll wait for another input from you because as you can imagine possibilities are more or less limitless :sweat_smile:

Then, i would suggest the creation of more table to manage all the aspect related to clients and agents (like you have done with the lookup of agents, the same concept but used more often along the doc)

So yeah, i’m stucked again, i need a doc or more inputs from you now :joy:
No ok, apart from jokes, if you want we can look at it together and i think that it’s gonna be the best way to get closer to your real needs, so please if you can prepare a “sharable doc” (or if that is not possible pm me so we can look at it privately) that we can “dismount and re-mount” together :smiley:

Have a wonderful day and making :star_struck:


1 Like

how do I add u/share the doc?


Hi @Manager_TPJ, thanks for sharing!
What a HUGE doc you’ve built! :smiley:

Sadly, now it’s “just see” so i cannot interact with your doc, for example copying it to put my hands on it, could you share a copy of it with the permission to copy it? Or, in case not, could you send me a link to it privately? :slight_smile:

Thanks! :slight_smile:

LOL… i’m learning…i’m more of a database guy so that’s how I think…

How do I share?
How do I send to you privately? I’m replying via the email thread…