*Google Sheets is a great platform to manage data tables. But are there any easy functions you can use for daily calculations? Find out below.*

I believe the most required operation in tables is to find the total sum of different values. The first thing that comes to mind is to add every single cell of interest:

`=E2+E4+E8+E13`

But this formula will become extremely time-consuming if there are too many cells to take into account.

The proper way to add cells is to use a special Google Sheets function - SUM - that lists all cells automatically using commas:

`=SUM(E2,E4,E8,E13)`

If the range consists of adjacent cells, simply indicate its first and last cells even if there are empty cells somewhere between. Thus, you'll avoid enumerating each and every cell in the formula.

The result will be inserted to a cell right below the selected range.

Let me complicate the task. I want to add numbers from different data ranges on multiple sheets, for example, *A4:A8* from *Sheet1* and *B4:B7* from *Sheet2*. And I want to sum them in a single cell:

`=SUM('Sheet1'!A4:A8,'Sheet2'!B4:B7)`

As you can see, I just added one more sheet into the formula and separated two different ranges by a comma.

I often hear people ask about finding the percentage of different totals. This is usually calculated by the percentage formula like this:

=Percentage/Total*100

The same also works whenever you need to check what part this or that number represents of the total:

=Part/Total*100

In my table where I keep records of all sales for the past 10 days, I can calculate the percentage of each sale from the total sales.

First, I go to E12 and find total sales:

`=SUM(E2:E11)`

Then, I check what part the first day sales constitutes of the total in F2:

`=E2/$E$12`

I recommend to make a few adjustments as well:

- Turn
*E2*to an absolute reference -*$E$12*- to make sure you divide each day's sale by the same total. - Apply the percent number format to cells in column F.
- Copy the formula from F2 to all cells below - up to F11.

`=SUM(F2:F11`

)

If it returns 100% - everything's correct.

Why do I recommend using the percentage format?

Well, on one hand, to avoid multiplying each result by 100 if you'd like to get percents. On the other, to avoid dividing the results to 100 if you want to use them for any further non-percent math operations.

Here's what I mean:

I use the percentage number format in cells C4, B10, and B15. All formulas referencing these cells are much easier. I don't have to divide by 100 or add the percent symbol (%) to formulas in C10 and C15.

The same cannot be said about C8, C9, and C14. I must make these extra adjustments to get the correct result.

To work with loads of data in Google Sheets, nested functions and other more complicated calculations are used as a rule.

For instance, I have a table of sales per client. I'm curious to find the maximum sale of milk chocolate to Smith to check if I can give him an extra discount next time. I use the next array formula in E18:

`=ArrayFormula(MAX(IF(($B$2:$B$13="Smith")*($C$2:$C$13="Milk Chocolate"),$E$2:$E$13,"")))`

I've got $259 as a result.

My first array formula in E16 returns the maximum purchase made by Smith - $366:

`=ArrayFormula(MAX(IF(($B$2:$B$13="Smith"),$E$2:$E$13)))`

E17 show the maximum money spent for milk chocolate - $518:

`=ArrayFormula(MAX(IF(($C$2:$C$13="Milk Chocolate"),$E$2:$E$13)))`

Now, I'm going to replace all values used in formulas with their cell references:

Have you noticed what has changed?

`=ArrayFormula(MAX(IF(($B$2:$B$13=B18)*($C$2:$C$13=C18),$E$2:$E$13,"")))`

Here's what I had before:

`=ArrayFormula(MAX(IF(($B$2:$B$13="Smith")*($C$2:$C$13="Milk Chocolate"),$E$2:$E$13,"")))`

Just like that, juggling with values in cells you reference you can quickly get different results based on different conditions without changing the formula itself.

Let's have a look at a few more functions and formulas examples hande for every-day use.

Suppose your data is written partly as numbers and partly as text: *300 euros*, *total - 400 dollars*. But you need to extract numbers only.

I know just a function for that:

=REGEXEXTRACT(text, regular_expression)

It pulls the text by mask with a regular expression.

*text*- it can be cell reference or any text in double quotes.*regular_expression*- your text mask. Also in double quotes. It lets you create almost any text scheme possible.

The text in my case is a cell with a data (*A2*). And I use this regular expression: *[0-9]+*

It means that I'm looking for any quantity (*+*) of numbers from 0 to 9 (*[0-9]*) written one after another:

If numbers has fractions, the regular expression will look like this:

`"[0-9]*\.[0-9]+[0-9]+"`

for numbers with two decimal places

`"[0-9]*\.[0-9]+"`

for numbers with one decimal place

Formulas within the text help getting a neatly looking row with some totals - numbers with their short descriptions.

I'm going to create such rows in lines 14 and 15. To start with, I merge cells in those rows via *Format > Merge cells* and then count the sum for column E:

`=SUM(E2:E13)`

Then I put the text I'd like to have as a description to double quotes and combine it with the formula using an ampersand:

`="Total chocolate sales: "&SUM(E2:E13)&" dollars"`

To make my numbers decimals, I use the TEXT function and set the format: *"#,##0"*

Another way is to use the CONCATENATE function, like I used in A15:

`=CONCATENATE("Total discount for customers: ",TEXT(SUM(F2:F13),"#.##")," dollars")`

What if you upload the data from somewhere and all numbers appear with spaces, like *8 544* instead of *8544*? Google Sheets will return these as text, you know.

Here's how to turn these values written as text to "normal numbers":

`=VALUE(SUBSTITUTE("8 544"," ",""))`

or

`=VALUE(SUBSTITUTE(A2," ",""))`

where A2 contains *8 544*.

How does it work? The SUBSTITUTE function replaces all spaces in the text (check the second argument - there's space in double quotes) with an "empty string" (the third argument). Then, VALUE converts text to numbers.

There are some functions that help to manipulate text in Google Sheets, for instance, change the case to sentence case. If you have something strange like *soURcE dAtA*, you can get *Source data* instead:

Let me explain that in detail. I take the first character in a cell:

`=LEFT(A1,1)`

and switch it to the upper case:

`=UPPER(LEFT(A1,1))`

Then I take the remaining text:

`=RIGHT(A1,LEN(A1)-1)`

and force it into lower case:

`=LOWER(RIGHT(A1,LEN(A1)-1))`

Lastly, I bring all pieces of the formula together with an ampersand:

`=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))`

Of course, there's much more Google Sheets has to offer. Don't be afraid of different complex formulas - just try and experiment. After all, these toolsets let us solve many different tasks. Good luck! :)

Excel formulas
Excel functions
Vlookup in Excel
Merge data in Excel
SumIf
Excel CountIf
Excel Compare
Excel If statement
Excel Charts
Pivot Table
Updates
Excel conditional formatting
Excel formatting
Excel time
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates