Skip to main content

Hey guys im really dumb in Excel, I need some help.



First column is the item, then next one I have records for each, that is the stock. I’m struggling for an hour to find a formula which COUNTS how many rows are there, therefore I have the exact numbers of the rows, which is the stock for each item.



Thanks!

Welcome to the community, @Tamas_Puskas!



Unlike Excel, Airtable doesn’t give us any formulas that can be used for counting/summarizing rows. The exception to this is the rollup field, but this only counts/summarizes rows of linked records from a linked table.



In your case, you will probably want to use the Summary Bar, which automatically gives you a variety of functions to use across rows. You can also group your records to get summaries of individual groups, as well.


Welcome to the community, @Tamas_Puskas!



Unlike Excel, Airtable doesn’t give us any formulas that can be used for counting/summarizing rows. The exception to this is the rollup field, but this only counts/summarizes rows of linked records from a linked table.



In your case, you will probably want to use the Summary Bar, which automatically gives you a variety of functions to use across rows. You can also group your records to get summaries of individual groups, as well.


Thanks a lot for helping me!



Maybe I wasn’t clear enough: what I want to achieve is the following.



I have a column lets say


A


this has multiple records


x


y


z



I have another column


B


this has multiple records for each:


x :


a


b


c



y:


c


d


e



z:


f


g



I would like to get a 3rd column which is STOCK:


x: 3


y: 3


z : 2


which changes everytime if I delete a row


Thanks a lot for helping me!



Maybe I wasn’t clear enough: what I want to achieve is the following.



I have a column lets say


A


this has multiple records


x


y


z



I have another column


B


this has multiple records for each:


x :


a


b


c



y:


c


d


e



z:


f


g



I would like to get a 3rd column which is STOCK:


x: 3


y: 3


z : 2


which changes everytime if I delete a row


I’m not sure I understand. Why don’t you post some screenshots or a video of your base?


I’m not sure I understand. Why don’t you post some screenshots or a video of your base?






So item1 has 4 rows, and I need an automatic counter in “Exact number of stocks” column, which counts how many are there cause its hidden. Item2 has only one item, so stock is 1.






So item1 has 4 rows, and I need an automatic counter in “Exact number of stocks” column, which counts how many are there cause its hidden. Item2 has only one item, so stock is 1.



Those aren’t rows, those are line breaks within a rich text field.



That is not the ideal way to setup a database system. The ideal way is to ACTUALLY make each item its own row (i.e. record) in ANOTHER table that you link to via a linked record field. And then you can count the number of linked records by using the count field.



Off the top of my head, I’m not sure how to count number of line breaks in a rich text field. Airtable doesn’t give us any function to count the number of times a character appears within a string of text. I know that you can substitute the line break for commas by using the formula SUBSTITUTE({Field Name},"\n",","), but I’m not sure where to go from there.



Perhaps someone else can chime in on a good trick for counting commas or line breaks, but in the meantime, I would highly recommend setting up your database with proper table & record structure.


Those aren’t rows, those are line breaks within a rich text field.



That is not the ideal way to setup a database system. The ideal way is to ACTUALLY make each item its own row (i.e. record) in ANOTHER table that you link to via a linked record field. And then you can count the number of linked records by using the count field.



Off the top of my head, I’m not sure how to count number of line breaks in a rich text field. Airtable doesn’t give us any function to count the number of times a character appears within a string of text. I know that you can substitute the line break for commas by using the formula SUBSTITUTE({Field Name},"\n",","), but I’m not sure where to go from there.



Perhaps someone else can chime in on a good trick for counting commas or line breaks, but in the meantime, I would highly recommend setting up your database with proper table & record structure.


Uhh okay, thank you very much!


Can you link me a guide to do that? Basically it’s just a simple thing, but I would really like if it looked something like this.


Here’s a good guide on how to get started with making tables & linking records:




Those aren’t rows, those are line breaks within a rich text field.



That is not the ideal way to setup a database system. The ideal way is to ACTUALLY make each item its own row (i.e. record) in ANOTHER table that you link to via a linked record field. And then you can count the number of linked records by using the count field.



Off the top of my head, I’m not sure how to count number of line breaks in a rich text field. Airtable doesn’t give us any function to count the number of times a character appears within a string of text. I know that you can substitute the line break for commas by using the formula SUBSTITUTE({Field Name},"\n",","), but I’m not sure where to go from there.



Perhaps someone else can chime in on a good trick for counting commas or line breaks, but in the meantime, I would highly recommend setting up your database with proper table & record structure.




I found a way to do this, thanks to @Justin_Barrett:







But — you should still go down the path of properly setting up your base! 🙂




I found a way to do this, thanks to @Justin_Barrett:







But — you should still go down the path of properly setting up your base! 🙂


Its still not working 😦 but thank you very much.



Also I checked that guide, but I still dont really get it. It seems too complicated. I would like to work on one table only. I think you understood what I was trying to do.


Its still not working 😦 but thank you very much.



Also I checked that guide, but I still dont really get it. It seems too complicated. I would like to work on one table only. I think you understood what I was trying to do.


To count the number of lines in your long text field, you can use this formula:



LEN({Field Name})-LEN(SUBSTITUTE({Field Name},"\n",""))+1


To count the number of lines in your long text field, you can use this formula:



LEN({Field Name})-LEN(SUBSTITUTE({Field Name},"\n",""))+1


@ScottWorld



Don’t forget to wrap your formula in an IF statement, or your formula will say that blank records have 1 line.



IF({Field Name},

LEN({Field Name})-LEN(SUBSTITUTE({Field Name},"\n",""))+1

)


@ScottWorld



Don’t forget to wrap your formula in an IF statement, or your formula will say that blank records have 1 line.



IF({Field Name},

LEN({Field Name})-LEN(SUBSTITUTE({Field Name},"\n",""))+1

)




Doesn’t work 😦 what should I put in field name, based on my example above?




Doesn’t work 😦 what should I put in field name, based on my example above?


Does Scott’s original formula work for you? If it doesn’t, my formula won’t work either.



I was simply wrapping Scott’s formula in an if function.


To count the number of lines in your long text field, you can use this formula:



LEN({Field Name})-LEN(SUBSTITUTE({Field Name},"\n",""))+1




doesnt really work 😦 what should I put in field name


@Tamas_Puskas You have to put in the actual name of your field. What is the name of your field? Is it STOCK_? If it’s STOCK_, then you have to put in STOCK_.


@Tamas_Puskas You have to put in the actual name of your field. What is the name of your field? Is it STOCK_? If it’s STOCK_, then you have to put in STOCK_.




Ohh nice, thank you so much!!! It works now. Only gotta take care of not pressing enter after last record, because that counts as one too




Ohh nice, thank you so much!!! It works now. Only gotta take care of not pressing enter after last record, because that counts as one too




This is how more complex formulas get built.


This version will ignore if you press enter after the last line.



IF({Field Name},

LEN(TRIM({Field Name}))-LEN(SUBSTITUTE(TRIM({Field Name}),"\n",""))+1

)




This is how more complex formulas get built.


This version will ignore if you press enter after the last line.



IF({Field Name},

LEN(TRIM({Field Name}))-LEN(SUBSTITUTE(TRIM({Field Name}),"\n",""))+1

)




WOW , thank you so much. This really made my life easier :grinning_face_with_big_eyes: thank you so much


Welcome to the community, @Tamas_Puskas!



Unlike Excel, Airtable doesn’t give us any formulas that can be used for counting/summarizing rows. The exception to this is the rollup field, but this only counts/summarizes rows of linked records from a linked table.



In your case, you will probably want to use the Summary Bar, which automatically gives you a variety of functions to use across rows. You can also group your records to get summaries of individual groups, as well.


Thanks @ScottWorld ! Is there a way we can programmaticaly access the summary data shown when we group records ? I'd like to access these summary data and update them regularly in a "report" table that will be read by another application. Many thanks in advance for your help !


@Nicolas_d_ANDRE

Unfortunately, Airtable has given us no way to programmatically access the data in the Airtable summary bar.

You would need to either: (1) recreate that information in a field (such as a count field or a rollup field), or (2) you would need to write your own custom Javascripts, or (3) my favorite no-code way is to use Make. You can use one of Make's aggregator tools to summarize data across records. There can be a bit of a learning curve with Make, which is why I created this basic navigation video for Make, along with providing the link to Make’s free training courses.

Also, you might like my Airtable training course, which you can take for free by signing up for a trial membership with LinkedIn Learning:  https://www.linkedin.com/learning/learning-airtable/ 


Reply