Tag Archives: SUMPRODUCT

The amazing SUMPRODUCT

Posted on June 3, 2017 by MF

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 , | 15 Comments

The logic gate

Posted on May 27, 2017 by MF

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 MF

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 MF

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

Make impossible possible

Posted on February 20, 2016 by MF

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 , , , , , | 2 Comments

SUM Time in the end of text strings

Posted on July 25, 2015 by MF

Did you encounter a task like this?  I did.  Actually it is not as difficult as you may expect.  It could be done in just a minute IF the data is not too bad, like the one shown above. I … Continue reading →

Rate this:

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

Perform VLOOKUP with 2 lookup values

Posted on May 11, 2014 by MF

4 different ways to perform LOOKUP with 2 lookup values We know that VLOOKUP is very useful. At the same time, we know that VLOOKUP has its limitations. E.g. VLOOKUP only looks from left to right; VLOOKUP only handle one … Continue reading →

Rate this:

Posted in Formula | Tagged , , , , | 38 Comments

Calculate number of a specific day between two dates

Posted on April 12, 2014 by MF

Answer: =SUMPRODUCT(–(TEXT(ROW(INDIRECT(B1&”:”&B2)),”DDDD”)=”Sunday”)) ‘where B1 is start date; B2 is end date. Excuse me? What it says?

Rate this:

Posted in Excel Tips, Formula | Tagged , , , | 16 Comments