=COUNTIF(D2:D500, “Caroline Forsey”) LEN Google Sheets Function If the author was in Column D, my formula would be: Or maybe I want to see how many blog posts were written by Caroline Forsey. Let’s say I’m curious how many blog posts received more than 1,000 views for this time period - I’d enter: With this up your sleeve, you’ll never have to manually count cells again. The COUNTIF formula tells you how many how many cells in a given range meet the criteria you’ve specified. =IFERROR((B2/C2, “ “) COUNTIF Google Sheets Formula So for the above situation, my formula would be: =IFERROR(original_formula, value_if_error) I typically use a space (“ “) so the sheet is as clean as possible. Using the IFERROR formula lets you replace the VALUE! Status with another value. This will show up as #VALUE! on your sheet, since you can’t divide by zero. You want to see the highest-converting pages, so you create a third column for page views divided by CTA clicks (or =B2/C2).Ībout one-third of your pages, however, don’t have any CTAs - so they haven’t gotten any clicks. To give you an idea, maybe you have two columns: one for page views and another for CTA clicks. Load Time’’!$1:$1000,2,FALSE)Īny time you’re using a formula where more than 10% of the return values lead to errors, your spreadsheet starts to look really messy (see the above screenshot!). I want to see if there’s any correlation between page speed and performance. In the second sheet, I have a report from Google Analytics with average page load time by URL.
In the first sheet, I have a list of blog posts, including their titles, URLs and monthly traffic. Let’s walk through an example, which should make this a bit easier to understand.
=VLOOKUP(search_criterion, array, index, sort_order) The only caveat: The data point must exist in both cells, and it must in the first column of the second sheet. The V-lookup formula looks for a data point - like, say, a blog post title or URL - in one sheet, and returns a relevant piece of information for that data point - like monthly views or conversion rate in another sheet.įor example, if I want to see how much traffic a specific set of blog posts got, I’ll export a list from Google Analytics, then put that list in another tab and use the V-LOOKUP function to pull views by URL into the first tab. V-lookups, are by far, the most useful formula in your tool-kit when you’re working with large amounts of data. Depending on the data, Google Sheets might suggest a formula and/or range for you. (If you want the formula for the entire row, this will probably be the first or second row in a column.)