SUMIF function

Assuming that in column B are some spendings of  people listed in column A, let's check how much Kate spent according this table:

	A	B
1	Kate	11
2	Kate	20
3	Mark	35
4	Ben	28
5	Kate	39
6	Mark	33

The formula is:

	A	B
1	Kate	=SUMIF(A1:A6,"Kate",B1:B6)

	A	B
1	Kate	70

COUNTIF function

Another simple one and useful ;)

	A	B
1	Green	=COUNTIF(A1,A$1$:A$6$)
2	White	=COUNTIF(A2,A$1$:A$6$)
3	Pink	=COUNTIF(A3,A$1$:A$6$)
4	White	=COUNTIF(A4,A$1$:A$6$)
5	Pink	=COUNTIF(A5,A$1$:A$6$)
6	Pink	=COUNTIF(A6,A$1$:A$6$)

And the answer is...

	A	B
1	Green	1
2	White	2
3	Pink	3
4	White	2
5	Pink	3
6	Pink	3

Or we can just check one value:

	A	B
1	Pink	=COUNTIF("Pink",A$1$:A$6$)

VLOOKUP function

This is the one that makes my work so much easier :). So what does it do? Searches for a value in the first column of a table array


Let's say these are our data - sheet1:

	A
1	Pink
2	Blue
3	Green

And this is our table array - sheet2:

	A	B	C
1	Green	10	Grass
2	White	14	Snow
3	Pink	25	Flower
4	Yellow	28	Sun
5	Blue	62	Sky

What we want to get are the values from column C (sheet2) for the data from column A (sheet1). C is the third column number in table array from which the matching value must be returned. $ - means that we bloked the range, otherwise the range would change when coping formula to other cells. Last value is FALSE cause we want to get an exact match.

	A	B
1	Pink	=VLOOKUP(A1,sheet2!A$1$:C$5$,3,FALSE)
2	Blue	=VLOOKUP(A2,sheet2!A$1$:C$5$,3,FALSE)
3	Green	=VLOOKUP(A3,sheet2!A$1$:C$5$,3,FALSE)

The formula returns:

	A	B
1	Pink	Flower
2	Blue	Sky
3	Green	Grass

RIGHT, LEFT and MID functions

Another helpful ones if we need just piece of data from the cells.

	A	B
1	LR279JR	=RIGHT(A1,2)
2	FI698LK	=LEFT(A2,3)
3	ZS873NB	=MID(A2,2,3)

result

	B
1	JR
2	FI6
3	S87

=RIGHT(A1,2)
A1 means where are the data
2 means how many characters we want from right side
=LEFT(A2,3)
A2 means where are the data
3 means how many characters we want from left side
=MID(A3,2,3)
A3 means where are the data
2 means that we want to start from second character
3 means how many characters we want need

CONCATENATE function

This text function is very helpful if we need to join several words or text strings together.

	A	B	C
1	1	0	=CONCATENATE(A1,B1)
2	2	1	=CONCATENATE(A2,B2,"A")
3	3	2	=CONCATENATE(A3," ",B3,"A")

result

	C
1	10
2	21A
3	3 2A