Google Sheets Functions List

1. Raw SEO Data Cleaning and Preparation

This type of task is often referred to as data cleaning, data wrangling, or data filtering, since it involves selecting relevant information from a larger dataset before conducting further analysis or making business decisions.

This formula combines all rows from Sheet1 and Sheet2 and returns only those where any column includes “development” or the substring “compan”, pasting them into Sheet 3 automatically.

=QUERY({Sheet1!A1:L;Sheet2!A1:L},”select * where Col1 contains ‘development’ or Col1 contains ‘compan'”,1)

By applying that formula, you’ve transformed a raw export from your SEO tools into a focused data set of interest.

Instead of sifting through the entire keyword dump, your Sheet 3 now contains only the rows from both sheets whose keywords (or other columns) include “development” or “compan”.

This condensed table makes it easier to analyze and prioritize keywords relevant to development-related services and companies, improving the efficiency of your SEO keyword research.

Sales qualified leads per month

Initial data

• the list of leads with different scoring for a particular month.

Task

• to count up how many leads with particular scoring are there.

=IMPORTRANGE("URL","January!A:A")

=COUNTIF(F:F,">=25")

COUNT cells that are not blank (and not count those ones that containes title)
=COUNTA(F:F")-1

=COUNTIF(IMPORTRANGE("url","Dec 2021!F:F"), ">=25")

=COUNTIF(IMPORTRANGE("url","Dec 2021!T:T"), "*referral*")

=countifs(F:F,">24",Q:Q,">0")

=arrayformula(CountIfs(ImportRange("url","February 2021!f:f"),">24",ImportRange("url","February 2021!o:o"),">0"))


=arrayformula(CountIfs(
ImportRange("url","February 2021!f:f"),">24",
ImportRange("url","February 2021!o:o"),">0",
ImportRange("url","February 2021!t:t"),"*referral*"))

COUNTIFS With OR For Multiple Criteria
=COUNTIF(
ImportRange("url","february 2020!t:t"),"*linkedin*")+COUNTIF(
ImportRange("url","february 2020!t:t"),"*referral*")

=UNIQUE(A2:A16) - copy and paste unique entries from the list with duplicate entries

=COUNTIF(A2:A16, C2) - count the number of unique queries

=QUERY(agregated!A:A,"select * where A contains 'sharepoint'",1)

=VLOOKUP(A386,KPnov01!$A$2:$J$856,2,false) Explanation: – search the value from A386 from the current tab (value=”php development company”) – in the range A2:A856 of another tab (tabname=KPnov01), – return a matching value of A386 from column B (2nd column in range) from KPnov01.

=IFERROR (VLOOKUP($A$2,$A$2:$B$10,2,0),"Not Found")

=CONCATENATE(A2,"/",B2)