Count number of specific single select in another table


#1

Hi,

I’m working on an accounts receivable table.

I have another table were I’ve marked a client is paid or unpaid. I’d like to count that in another table.

How can I get this done? I was close with a rollup field but can’t seem to do it.


#2

It would be helpful to know more details about how you are trying to use the counted ‘Paid/Unpaid’ information but here is a brief solution that might work:

In the Table with the Single Select field, you could make a new formula column/field that would change based on the Single Select field (Paid or Unpaid). An example of this formula is below where Paid/Unpaid is in a column/field I titled, ‘Status’ for the purpose of this example:

IF({Status}=“Paid”,1,blank())

This would give you a number ‘1’ in this new column for each record that has the Single Select as ‘Paid,’ and will be blank for ‘Unpaid.’ This can then be pulled to your other table using a Lookup or Rollup field depending on what purpose it needs to serve.

If you wanted a count of the ‘Unpaid’ clients, this could be accomplished with a similar formula, switching “Paid” for “Unpaid,” and you could change the resulting ‘1’ or ‘blank())’ as well depending on the information you need.


#3

Thanks, will give this a go, seems functional, not too elegant but I guess that’s what the hide field is for.

I was hoping I could count the number of paid or unpaids in the other table with a formula so that would be my easy multiplier.

If I make paid/unpaid a checkbox is there a way to do?

What I’m trying to do is calculate how much income is yet to come in. So count the number of unpaids, and in another field I have an amount, so this amount x paid, and unpaid to see how much has come in and how much has yet to be paid.


#4

I threw together a quick example with different Rollups so you can see the difference. There is a way to use a checkbox field to get similar results (Airtable default is if a box is checked the value is 1; unchecked is 0).


#5

Awesome, didn’t know that about checkbox, that will work for my purposes then, thanks for the responses I can resolve what I need with this info!

-r