You are gonna want to see how much of anything you have in various columns.
Most often, you will want to count categorical data. For instance, in the Airbnb data you might want to know how many properties there are in Manhattan listed on Airbnb in 2019, you can scroll down to the end of the column and in put the following formula:
=countif(E2:E48896, “Manhattan”)
If you enter that, you would get 21,661. That’s a lot for 48,896 listings!
This function will count instances of a number or instances of a text or what’s called a “string.” Strings have to be in quotes but numbers do not have to be.
So, you could do this under minimum_nights:
=countif(k2:k48896, 1)
You should get 12,720 listings that require a minimum of 1 night to stay.
What precedes the value (in this case, “Manhattan” or 1) is the range of cells you want it to count in. For such a big file like the Airbnb data set is, I would recommend just typing it in rather than dragging the cursor, which you can also do.
You write the range as the column letter plus the cell number. Notice that both examples start at cell 2 and not cell 1. That is because you don’t want to count what is in the column title, which is often in the first cell of the column. Depending on your data set, you might want to start later if there are a few rows above the beginning of the entry of values.
Other Notable Formulas
You can also use something called “=countifs” which allows you to add additional criteria for something you want to count. For instance, perhaps you only want to count Manhattan listings that have a price lower than $150.
You would write something like:
=countifs(e2:e48896, “Manhattan”, j2:j48896, “<100”)
If you try this, you should get 6,036 listings.
You might also want to add up all of the numeric values in a column. For instance, if each row was the number of visits a person made to a tutoring center, you might want to add up the whole column so you can have the total number of visits that were made to the tutoring center.
To do that, you use the =sum() formula. You would just put the range of cells in the parentheses.
Another formula that can be useful to find a percentage is to divide values. So, perhaps you know how many times Manhattan is listed in the Airbnb data set and you know how many listings there are (just count the rows). You can do this:
=21661/48895
Then you would get 0.44, so 44% of listings were in Manhattan in 2019.
Instead of typing out the number, you can also highlight the cell that has the countif value (for me, this was =E48897/48895)