You only still use the function sumif.. You'd better use the function sumifs......

Still, many people use the function sumif.
Yes, the function is very useful.
I sometimes ask people to use the function "sumifs".
But they do not know how to use it.

Let me tell you.

The function is more powerful than sumif.

For instance,

See below and think about sumif and sumifs.

Can you calculate how much HR spent?

Yes, it is very easy.
If you use the function sumif, the formula is =SUMIF(C6:C12,"HR",F6:F12).
However, when you use the function sumifs, the formula is =SUMIFS(F6:F12,C6:C12,"HR")

Can I give you another question?
If you want to know that how much HR spent for January, which one is better?
Absolutely, you use sumifs than sumif. Of course, you use the function sumif.
But I do not recommend it.

Let's take a look at the picture below.

The function sumifs deals with many critical.

What if you want to know that how much Henry at HR spent on January?

Let's look at the picture below.

You just put the range and critical.

Please use the function sumifs...

Can you solve this question by using the function SUMPRODUCT?

In the office, people are using EXCEL. But have you ever used the function SUMPRODUCT?

I'll give you a question.

Please use SUMPRODUCT..

See the pic-1

Can you solve the total?

In my experience, many people use simple way such as the pic-2

I am serious. Yes, lots of people do it.

Now, let's use the function SUMPRODUCT.

see below.

Do you think it would be better than other one?

How to find same strings whether or not at different cells

To solve this question,

We have to use the function "EXACT"...

See below,

Let's use short cut keys when you use copy and paste....

We use copy and paste short cut keys many times.
Yet, keep practicing when you are in the office...

Excel 2007

Values : Ctrl+C -> select a cell if you want to paste -> Alt+E ->S ->V-> Enter
Formulas : Ctrl+C -> select a cell if you want to paste -> Alt+E ->S ->F-> Enter 

Formats :  Ctrl+C -> select a cell if you want to paste -> Alt+E ->S ->T-> Enter
 
Excel 2003 

Values : Ctrl+C -> select a cell if you want to paste -> Alt+S ->V-> Enter
Formulas : Ctrl+C -> select a cell if you want to paste -> Alt+S->F-> Enter 

Formats :  Ctrl+C -> select a cell if you want to paste -> Alt+S->T-> Enter

Why has a cell the value because the cell is empty when we see the cell?

Sometimes, a cell is empty when we see it, but the cell is indicated as an no empty cell.
You think it is strange.
What happens with the cell? However, the cell is not empty. It has a data like character size is 0.

For instance, Suppose if you input the formula such as =if(A1="","",A1). If A1 cell is empty or has empty character string, the formula returns the empty character string like the length is 0. Again, we cannot see the data but there is text input.

Now, copy the cell and check the values by using paste special.
After that, the cell is transformed into values and it is stored as a empty character string as not seen.

So, if you want to make an empty cell, just use Del key.

First

This blog is about EXCEL,VBA and DATABASE.