Using a formula is easy, at least, in its basic function, but the game will start to be difficult when the formula doesn’t display what you need. In such situation, you will try to find out a way. I remembered the first time I am using excel and getting involved in the beautiful world of formula. I was happy to be able to sum in an easy way with the formula but one day, I didn’t get what I want and some questions started to flow.

How can I get this result ? Can I combine multiple formulas ? Which function is best for a better result ? So many more questions with one final goal, to get the final result I need by creating customized formula.

I am not going to describe all functions or how each one works because those information, you can find in the Microsoft webpage but what you can’t find, it is the “tips” that will give you this plus.

So you are using the first time excel or you used it many years now but never entered in the “difficult” world of personalized formulas. For a better understanding, imagine I extracted this data from my service management tools.

The easy thing to do for instance is to get the total of “number”, you will write in the “formula bar” (green), this “=E2+E3+E4+E5” for the cell G2 but the quicker way is to use the “sum” function “=SUM(E2:E5)”.

This is basic and simple but what about “I want to know the total of the team2 for the group2” or “I just want the total for the day 01 and for the day 01 + 02” or “who did the ID 101 in group1”. OK you will tell me to use my brain !!! because the example is simple but imagine the data is for the full month or 1 year !!! now not telling me anything !!!

The most difficult is to know which function to use, you can use:

• The function option

• Or in the formula bar, put “=” and the first letter to get a list of all functions starting with this letter, for instance, “=c”.

If you put more letters, for instance “=cou”, the list of options will reduce providing you a smaller list that will match what you need for. Normally, if you put the name of what you want to do, you will get the correct function, for instance, you want to count, so put “=count”, I want this if the other not working, so “=if”, I want this and this so “=and”, etc.

Let’s answer the 4 questions:

• I want to know the total of the team2 for the group2
  =SUMPRODUCT((D2:D5="team2")*(E2:E5))
• I just want the total for the day 01
  =SUMIFS(E2:E5,B2:B5,"=01.06.2019")
• I just want the total for the day 01 + 02
  =SUMIFS(E2:E5,B2:B5,">=01.06.2019",B2:B5,"<=02.06.2019")
• Who did the ID 101 in group1
  =VLOOKUP(101,A:E,3,0)

You can combine 1 formula into another one, in fact, you can put many formulas as you want into 1 formula. For instance, as you can see, there is an empty cell in the group1, if you want to display something else than an empty cell:

=IF(C5="","no team",C5)

Now imagine that instead to display “no team”, I want to display the team in the group2:

=IF(C5="",IF(D5="","",D5),C5)

As you can see, I put another formula, in this case, I put again “IF”.

NOTE: depending of your excel version, you may put “;” instead of “,”.

The good and bad thing about excel is that if you copy the cell containing the formula, and paste it to another cell, the formula will update itself. By putting the $ sign before, after or between the letter and number, you will lock it so no matter where you will paste the formula, the formula will not change. For instance:

• $C$5 locking the entire cell
• C$5 locking only the cellnumber meaning the row
• $C5 locking only the cellletter meaning the column
• $A:$E locking bothcolumns

You can use a wildcard or asterisk or a star, whatever you call it, I mean that “*”. For instance:

• =VLOOKUP("*am2",C2:E5,3,0)
so looking for any words ending with “am2”
• =VLOOKUP("tea*",C2:E5,3,0)
so looking for any words starting with “tea”
• =VLOOKUP("*eam*",C2:E5,3,0)
so looking for any words containing “eam”

Sometimes, when I extracted a data, I like to keep the original sheet and do everything with a new one, in this case, the formula should reference it. For instance, I will put those formulas in the new created sheet:

• =SUM(Sheet1!E2:E5) if the original sheet name is 1 single word
• =SUM('Sheet original'!E2:E5) if the original sheet name has space between the words

There are so many functions and so many ways, from the simple formula to a combination of formulas to get the result we need. This topic is just to give you a glimpse of things you can do. Don’t limit to 1 formula, use 2 or 3 or more formulas into 1 single one to get what you want.

If I am using too much complicated formulas, excel will be slow each time that I want to put a new data and/or to update a cell. This is because it will always calculate the results automatically. In such situation, I will de-activate the “automatic calculation” to speed it.To do it:

Formulas -> Calculation options -> manual



By selecting “manual”, the results will be updated only when I will save the file or when I click on “calculate now” and/or “calculate sheet”.

One last thing, may be you don’t need to use formula because with the data extracted from your incident management tools, you are satisfy. In this case, the easy and best way to calculate everything, it is to use the pivot table option. In this case, read my topic Use a pivottable in an excel report.