Here's why you need to stop using the SUMIF function in Excel!

𝘌𝘷𝘦𝘳𝘺𝘰𝘯𝘦 knows SUM. After all, it was almost certainly the first function you learnt in Excel.

While it's useful for calculating a whole column of numeric values, that isn't always what you want.

A frequent task is to total up a column based on one or more conditions, and there are two functions for this: SUMIF and SUMIFS.

𝗦𝘆𝗻𝘁𝗮𝘅𝗲𝘀

=𝚂𝚄𝙼𝙸𝙵(𝚛𝚊𝚗𝚐𝚎,𝚌𝚛𝚒𝚝𝚎𝚛𝚒𝚊,[𝚜𝚞𝚖_𝚛𝚊𝚗𝚐𝚎])
=𝚂𝚄𝙼𝙸𝙵𝚂(𝚜𝚞𝚖_𝚛𝚊𝚗𝚐𝚎,𝚌𝚛𝚒𝚝𝚎𝚛𝚒𝚊_𝚛𝚊𝚗𝚐𝚎,𝚌𝚛𝚒𝚝𝚎𝚛𝚒𝚊,...)

In the video example, a table called tblSales houses some sales data. SUMIF is used to sum the Sales rows where the corresponding values in the Region column are equal to 'North'.

However, it's then decided an additional criterion is needed to only consider Month rows equal to 'Mar'.

This is where SUMIFS comes in. The only fundamental difference is SUMIF handles a single criterion, whereas SUMIFS can deal with multiple criteria.

However, it can cause confusion because the 𝚜𝚞𝚖_𝚛𝚊𝚗𝚐𝚎 argument comes last in SUMIF but first in SUMIFS.

Why?

SUMIFS appeared in Excel 2007 as an alternative to SUMIF. As SUMIFS was made to support up to 127 criteria, it probably didn't make much sense to put 𝚜𝚞𝚖_𝚛𝚊𝚗𝚐𝚎 last, hence the position change.

Is it time SUMIF was removed? Oh, and COUNTIF, for that matter.

Tell us in the comments!

#exceleration #excel #microsoftexcel #excelformulas #exceltips #globalexcelsummit

---

The Global Excel Summit is the world's largest gathering of Microsoft Excel users and experts.

Find us on: