Tag Archives: SUMPRODUCT

Make impossible possible

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

SUM Time in the end of text strings

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

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

Calculate number of a specific day between two dates

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