Tag Archives: COUNTIF

The INDIRECT trick of using Structured Reference in Conditional Formatting

Posted on January 19, 2019 by MF

When setting up conditions for Conditional Formatting in Excel, have you ever tried to set a formula that refers to a Excel Table, but failed?

First of all, why we want to refer to Excel Table in Conditional Formatting? There is one simple answer: To accept new data automatically. 
To use Structure Reference in Conditional Formatting, we need an INDIRECT approach which is discussed here. Continue reading →

Rate this:

Posted in Excel Tips | Tagged , , , | 7 Comments

Highlight weekends and holidays using Conditional Formatting in #Excel

Posted on January 6, 2019 by MF

In this post, I will show you step by step how to set up conditional formatting to highlight weekends and public holidays with Excel. The key is to set the formula right. Having a proper layout will make the whole thing more easier, which will be discussed in the next post. Continue reading →

Rate this:

Posted in Format | Tagged , , , | 4 Comments

Rank in subgroup… RANKIF?

Posted on March 12, 2016 by MF

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

VLOOKUP the Nth matched item, with helper column

Posted on December 24, 2015 by MF

This should be the last post in 2015.  So it’s better to answer unanswered question in the year.  🙂 Question: “VLOOKUP will return the first matched value found, in case there are multiple matched records what formula should we use?” This … Continue reading →

Rate this:

Posted in Formula | Tagged , | Leave a comment

15-significant-digit issue with SUMIF(S), COUNTIF(S), AVERAGEIF(S)

Posted on January 24, 2015 by MF

Have you applied SUMIF or COUNTIF to a range that contains numbers with more than 15 digits stored as text? If you did or if you do, you’d better continue to read this as you’ve got wrong answers that you … Continue reading →

Rate this:

Posted in Formula | Tagged , , , , | 10 Comments