Skip to main content

Countif alternative - adding totals in one column based on dropdown status in another?

  • December 20, 2021
  • 4 replies
  • 58 views
Andrew_Carville
Forum|alt.badge.img+9

Hi all,

I’m trying to work out a simple countif alternative which adds numbers in a column only based on a dropdown selection in another column. For example, if I have dropdown options based on project status (ie. “Not Started”, “In Progress” and “Complete”), and a simple number column tracking how many apartments are in each project, can I have a third column with a formula counting the number of apartments only in Complete projects, which I can then use to create a dashboard display?

Or do I have to create a separate view and sort/filter the data this way. Keen to avoid as I already have quite a few different views set up.

Thanks in advance!
AC

Karlstens
D

4 replies

Rupert_Hoffsch1
Forum|alt.badge.img+21

Hi AC,

so basically you can run a formula with a simple if statement on those two fields (status and apartment number):

IF({Status} = "complete", {ApartmentNumber})

This way you’re only displaying those values in the CountIF field where the status in the status field is set to "complete.

How are you planning to build the dashboard? For instance Airtable’s native “Summary” app would be able to summarize that field and then only display the number of apartments were projects are set to “complete”. Airtable’s interfaces app has that functionality as well of course.

Andrew_Carville
D
Andrew_Carville
Forum|alt.badge.img+9
  • Andrew_Carville
  • Author
  • Known Participant
  • December 21, 2021

Hi AC,

so basically you can run a formula with a simple if statement on those two fields (status and apartment number):

IF({Status} = "complete", {ApartmentNumber})

This way you’re only displaying those values in the CountIF field where the status in the status field is set to "complete.

How are you planning to build the dashboard? For instance Airtable’s native “Summary” app would be able to summarize that field and then only display the number of apartments were projects are set to “complete”. Airtable’s interfaces app has that functionality as well of course.


Hi Rupert - that’s perfect, exactly what I was trying to work out. Thanks for your help!

I was using the Summary app in the dashboard, but it doesn’t appear to be able to summarise based on the status of a drop down menu (ie. which projects were “Complete”). That’s why I created the column for your formula above - to isolate on the “Complete” apartments first. Is there a better way to achieve it?

Thanks again,
AC

D
Rupert_Hoffsch1
Forum|alt.badge.img+21

Hi Rupert - that’s perfect, exactly what I was trying to work out. Thanks for your help!

I was using the Summary app in the dashboard, but it doesn’t appear to be able to summarise based on the status of a drop down menu (ie. which projects were “Complete”). That’s why I created the column for your formula above - to isolate on the “Complete” apartments first. Is there a better way to achieve it?

Thanks again,
AC


Hi AC,

exactly, in order for the summary field to work you have to calculate the value first. The summary field simply runs a formula on all entries of a given field.

An alternative would be to use the pivot table, which you might know from spreadsheets. This way you would also be able to show the total of apartments, even without calculating in a separate field before.

Lastly, I want to mention Airtable’s interfaces app, which would allow you to build a simple dashboard based on your data.

Best,
Rupert

Andrew_Carville
D
D
Forum|alt.badge.img+1
  • DavidIatTMO
  • New Participant
  • February 3, 2023

Hi AC,

exactly, in order for the summary field to work you have to calculate the value first. The summary field simply runs a formula on all entries of a given field.

An alternative would be to use the pivot table, which you might know from spreadsheets. This way you would also be able to show the total of apartments, even without calculating in a separate field before.

Lastly, I want to mention Airtable’s interfaces app, which would allow you to build a simple dashboard based on your data.

Best,
Rupert


I just wanted to let you know out of the five or so threads I've looked at to do something just like Andrew, you're the first I've felt had some great solutions. Thank you!

Terms & ConditionsAccessibility statement