Tag Archives: SUMIF

The strange behavior of SUMIF and the quick fix

Posted on June 3, 2022 by MF

Have you ever got an incorrect result from SUMIF and wonder why? You can find the answer in this post. 🙂 Continue reading →

Rate this:

Posted in Formula | Tagged | Leave a comment

The power of Dynamic Arrays in #Excel 365

Posted on October 26, 2019 by MF

… in creating dynamic drop-down with conditions Making a dynamic drop-down list is not difficult.  It may require multiple steps, helper formula and/or helper tables though.  And the number of helper tables depends on the complexity of the requirement.  I … Continue reading →

Rate this:

Posted in Excel in Action, Excel Tips | 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

2D SUMIF with two variables – one on column and one on row

Posted on September 20, 2014 by MF

SUMIF is a commonly used function to give you sum of a range that meets a specific criterion.  For the example above, if you want to know the total expense of Department 1 in Jan, you can use SUMIF: The Syntax … Continue reading →

Rate this:

Posted in Formula | Tagged , , , | 12 Comments

SUM ignoring errors in the range

Posted on March 22, 2014 by MF

Different ways in getting sum of a range with “Error” Thanks to the comments made by a friend, I would like to share a few workarounds to ignore errors with SUM. 1) Use a helper column This would be an … Continue reading →

Rate this:

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