Category Archives: Formula

The amazing SUMPRODUCT

Posted on June 3, 2017 by

Advanced use of SUMPRODUCT for multi-conditional SUM In the previous posts, we talked about the basic of SUMPRODUCT, the behaviors of logical  values (i.e. TRUE/FALSE) when they are put into basic mathematics operations.  These set the foundation to understand the … Continue reading →

Rate this:

Posted in Formula | Tagged , | 2 Comments

The logic gate

Posted on May 27, 2017 by

Have you heard about Logic Gate?  I learned logic gate in high school physics.  Decades ago… so don’t expect I can remember what it is.  😛 Having said that, I do remember the fundamental concept about AND gate and OR gate, … Continue reading →

Rate this:

Posted in Formula | Tagged , | 4 Comments

SUM the PRODUCTs or SUMPRODUCT? That’s the question.

Posted on May 20, 2017 by

The basic of SUMPRODUCT Suppose we have a column showing retail price of different items; another column showing the units sold (illustrated above).  To get the total sales, most people will deploy a helper column to get the sales of each … Continue reading →

Rate this:

Posted in Formula | Tagged | 2 Comments

CrossTab SUM with 3 variables

Posted on May 13, 2017 by

This post is trying to answer a question by Laura in the post of 2D SUMIF with two variables – one on column and one on row. Here’s part of the question: ……I want to look up the department in the vertical … Continue reading →

Rate this:

Posted in Formula | Tagged | 3 Comments

Transpose Data (Static and Linked)

Posted on January 14, 2017 by

Answer to the 5 little Tricks posted in the beginning of the year – Part 1/5 Transpose Data – Result is static This is actually a simple trick of using Copy and Paster (Special, Transpose).  See? It can be done in a … Continue reading →

Rate this:

Posted in Excel Tips, Formula | Tagged , , , , | Leave a comment

Be cautious when using SUMIF(s)

Posted on November 20, 2016 by

SUMIF is a handy but helpful function.  The syntax is simple: =SUMIF(range,criteria,[sum range] What it does is quite straight forward indeed.  In the example above, it instructs Excel to look into the range (A2:A7), look for the matching criteria, which … Continue reading →

Rate this:

Posted in Excel Tips, Formula | 2 Comments

Writing a long formula in steps

Posted on August 13, 2016 by

Writing a long formula is not easy, even for an advanced Excel user.  What I mean long is  a formula with many nested FUNCTIONS within a single formula.  The difficulty I am talking about is not related to whether you understand the … Continue reading →

Rate this:

Posted in Formula | Tagged , , , , , | Leave a comment

Text manipulation with Excel functions

Posted on July 23, 2016 by

We talked about RIGHT, LEFT, MID, UPPER, LOWER, PROPER, FIND, SEARCH, REPLACE and SUBSTITUTE in the past few weeks.  Now it’s time to put every thing together to solve a problem.  This is actually the most amazing part of using … Continue reading →

Rate this:

Posted in Formula | Tagged , , , , , , , , , , | 6 Comments

Case-insensitive SUBSTITUTE???…

Posted on July 16, 2016 by

We talked about REPLACE and SUBSTITUTE last week.  At the final note, I said that SUBSTITUTE is case-sensitive.  If we need to perform a case-insensitive SUBSTITUTE, it is indeed not an easy job unless we are talking a single letter … Continue reading →

Rate this:

Posted in Formula | Tagged , , | 6 Comments

REPLACE vs. SUBSTITUTE in Excel

Posted on July 9, 2016 by

Another pair of functions that has very similar meaning literally.   Although I am not able to tell you the differences between “replace” and “substitute” in English, I can show you the differences of the REPLACE and SUBSTITUTE in Excel. … Continue reading →

Rate this:

Posted in Formula | Tagged , | 2 Comments

FIND vs. SEARCH

Posted on July 3, 2016 by

In many situations, we use either FIND or SEARCH together with LEFT, RIGHT, MID to achieve what we need.  A typical example is to extract the first name and the last name from a full name where a coma acts as … Continue reading →

Rate this:

Posted in Formula | Tagged , | Leave a comment

UPPER, lower, Proper

Posted on June 25, 2016 by

I am wondering why these functions are not available in Word, but Excel? Don’t make me wrong.  I like these functions.  Nevertheless I am not a big fan (nor a small fan) of using Excel as word processor.  Whenever possible, … Continue reading →

Rate this:

Posted in Formula | Tagged , , | 8 Comments

RIGHT. I LEFT. In the MID of…

Posted on June 18, 2016 by

Obviously this post is about the most popular text related functions in Excel.  Meanwhile, it is also a message from me… Yes, you are RIGH! I LEFT my ex-company and have landed on a new job which is much more … Continue reading →

Rate this:

Posted in Formula | Tagged , , | Leave a comment

Display single letter Day of week in Excel

Posted on May 21, 2016 by

Is it achievable?  Not by formatting but formula.

Rate this:

Posted in Format, Formula | Tagged , , , | Leave a comment

Number Stored as Text…

Posted on May 14, 2016 by

It is something basic and commonly seen.  Nonetheless,  it is really important to understand the different behaviors between a number, and a number stored as text, especially if you want to move forward to write effective formula.  This post is intended … Continue reading →

Rate this:

Posted in Excel Basic, Formula | Tagged , , | 5 Comments

Three different ways to do case-sensitive lookup

Posted on April 9, 2016 by

If you do VLOOKUP, you probably know that one of the limitations of VLOOKUP is case-insensitive.  It means case is not a consideration in the lookup process, where PETER is essentially the same as peter. Hence VLOOKUP will consider that … Continue reading →

Rate this:

Posted in Formula | Tagged , , , , , , | Leave a comment

Rank in subgroup… RANKIF?

Posted on March 12, 2016 by

To get the overall ranking is easy with the RANK function.  The syntax of RANK =RANK(Number,Ref,[order]) Number is the number we want to rank; Ref is the list of numbers of comparison (could be an array, a reference or a list … Continue reading →

Rate this:

Posted in Formula | Tagged , , | Leave a comment

Make impossible possible

Posted on February 20, 2016 by

Convert text of specific pattern like “1d 2h 3m 4s” into real time (26:03:04) in Excel To kick start the Year of Monkey, let’s challenge the apparently impossible… Do you think it is not possible in Excel?  If you do, you … Continue reading →

Rate this:

Posted in Formula | Tagged , , , , , | Leave a comment

Calculate number of “*day” in a given period

Posted on February 6, 2016 by

This post is about how to twist the NETWORKDAYS.INTL function to solve the question. About two years ago, I wrote a post <Calculate number of a specific day between two dates> to explain how the following formula (Solution 1) could solve the question: … Continue reading →

Rate this:

Posted in Formula | Tagged , | 6 Comments

Display % of column and % of row in a dynamic way

Posted on January 16, 2016 by

This post is intended to give you an introduction of CHOOSE function and Option Button (Form Control). Here’s the situation: We want to display the % of total in a 2-D table.  Nevertheless the % can be of column total … Continue reading →

Rate this:

Posted in Excel Tips, Formula | Tagged , | Leave a comment