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