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

do while / VBA

This is very simple example of using  do while. When you have some data in sheet1(named data) and you want to get results in sheet2 (named results) and the data is lying in A and B columns of sheet1.


data

	A	B
1	1	0
2	2	1
3	3	2

results

	A
1	1
2	1
3	1

Sub

Dim i as long

i=1

Do while Sheet1.Cells(i,1)<>""

Sheet2.Cells(i,1)= Sheet1.Cells(i,1)-Sheet1.Cells(i,2)

i=i+1

Loop

End Sub


When the sheets are named it is much easier to use these names. So let's see the same formula but this time the names are used.

Sub SubExample()

Dim i as long

i=1

Do while Sheets("data").Cells(i,1)<>""

Sheets("results").Cells(i,1)= Sheets("data").Cells(i,1)-Sheets("data").Cells(i,2)

i=i+1

Loop

End Sub

Now let's change the sign and instead of "-" let's use "&".  We get similar results when we use CONCATENATE excel function.

Sheets("results").Cells(i,1)= Sheets("data").Cells(i,1)& Sheets("data").Cells(i,2)

results

	A
1	10
2	21
3	32