Google Sheet Formulas

Google Sheet is definitely is better than Microsoft Excel. I believe, the most of the people will use Google Sheet instead of Excel. I would like to show some useful formulas to you!
  • vlookup: It finds the wanted value on ONLY column 1. Then, pick up the data on column.
  • hlookup: Searches across the first row of a range for a key and returns the value of a specified cell in the column found. 
  • index(match (), XXX)
  • transpose: make vertical to horizantal OR horizantal to vertical
  • countblank: It counts all blank cells.
  • ARRAYFORMULA: Once it is used, there must be another formula in it. arrayformula (another formula). This formula applies inside formula all selected range.
  • counta: It counts not empty cells.
  • sumifs: conditional sum. Please note that the number of selected range must be same. Otherwise, the formula will not work properly. EX: sumifs(A2:A ...... C:D) is wrong. It should be like. sumifs(A:A ...... C:D) OR sumifs(A2:A ...... C2:D) 
  • countifs: conditional count. Please note that the number of selected range must be same. Otherwise, the formula will not work properly. EX: countifs(A2:A ...... C:D) is wrong. It should be like. countifs(A:A ...... C:D) OR countifs(A2:A ...... C2:D) 
  • sort
  • FILTER: You can also use it as a query. Query only takes data. If you did a query by using filter formula, it also takes hyperlinks.
  • unique: remove repeating items
  • days360: convert days to number. So you can work on days easily in the formula.
  • today()-6 means 6 day before today.
  • QUERY: uppercase and lowercase is important. col3 doesn't work but Col3 definitely works. Another important point: If you want query with a formula, you should type Col1, Col2,...etc. If you use it straight (basic), you should use A,B,...etc. Take a look on the following links to understand query:
    https://productforums.google.com/forum/#!topic/docs/WZjUsPCcDFQ
    https://productforums.google.com/forum/#!msg/docs/psflKQCtAwU/xFqhzVISNwAJ
  • Upper/Lower/PROPER(text_to_capitalize): a specified string to uppercase
  • Split: Split the word. You can also use Data>Split Text to Columns
  • Len: Length of the word
  • LEFT, RIGHT: To change the word. Kirpmak icin :)
  • Substitute: Change any character of the word.
Please note that once you press = and type the formula name you will get some clues about the formula. Actually, everything what you need is in there. If you need more help, just ask GOOGLE or YouTube. 😊

Comments

Popular posts from this blog

iBoss Tips&Problems

Synology DiskStation Quick Installation

Sonicwall; Open a port and how to use sonicwall wizard