SUMIF versus SUMIFS

SUMIF allows to sum using a single criteria, where SUMIFS works with multiple criteria.

I tend to find that on many occasions, I've start with SUMIF and then need to add more criteria, so change the formula to SUMIFS which requires a minor corrections.

Ignoring issues of backwards compatibility with older versions of Excel, would there be any reason not to exclusively use SUMIFS and COUNTIFS, such as requires more processing time, or memory issues etc.

1 Answer

::Caveat::

Since you have not provided any sample data, as well what exactly you are trying to achieve, therefore I'm trying to highlight fine line between these two.

• Both works, based on conditions.
• One produces count as well as other sums.
• SUMIFS is an extension of SUMIF, as well COUNTIFS is add on to COUNTIF.
• But both can be combined for common cause.

Here I would like to show sum examples.

• Formula in H130:

  =COUNTIF(H125:H128,"<20")+COUNTIF(H125:H128,">30")

Here if use the COUNTIFS, will produce ZERO.

• An array (CSE) formula in H131:

  {=SUM(COUNTIF(H125:H128,{"<20",">30"}))}

Finish formula with Ctrl+Shift+Enter.

• This can be achieved by SUMPRODUCT also.

• Formula in cell H132:

  =SUMPRODUCT((H125:H128<20)+(H125:H128>30))

Now, I would like to show how SUMIF can use with COUNIF.

• Formula in K134:

  =COUNTIF(J125:J132,"A")

• If I combine both and use this one in cell K135.

  =SUMIF(J125:J132,COUNTIF(J125:J132,"A"),K125:K132)

Here COUNIF is working as criteria, but the formula produces ZERO, since combination doesn't works.

• But if consider the formula in cell K136 works, since got proper combination.

  =IF(COUNTIF(J125:J132,"A")>0,SUMIF(J125:J132,"A",K125:K132),"")

• An alternate formula in cell K137:

  =SUMPRODUCT((J125:J132="A")*(K125:K132))

N.B.

• I do believe, all these clear the picture in your mind.
• Let me remind that processing time or memory is not a core issue, unless you are not handling a larger set of data.
• Before getting judgmental, you and readers may raise points through comments, in case this differs to the issue.