SpeakersAgendaMasterclassesVisitor Info
Partners
Event PartnersParticipating Companies
ContactGet Tickets
0

Your Cart

  • :
Remove
Subtotal:
Pay with browser.
Continue to Checkout
No items found.
Product is not available in this quantity.
June 8, 2023

Why you should stop using SUMIF

Everyone 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.

Syntaxes

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

@globalexcelsummit 𝘌𝘷𝘦𝘳𝘺𝘰𝘯𝘦 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 ♬ original sound - globalexcelsummit

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. Let us know!

Join the Master Club

Your exclusive all-access pass to our entire digital learning experience for a whole year.

Get 365-Day Digital Pass
Pages
Home
20212022202320242025
About UsFAQsOnline CoursesBlogContact
LEGALS
Privacy PolicyTerms of Service
Subscribe to our newsletter!
The Global Excel Summit is a premium global conference bringing together thebrightest minds from the world of Excel.
© 2025 The Global Excel Summit is organised and managed by DATEL Productions Limited, a company incorporatedin England and Wales (no. 10106893) and registered at Suite 407, 95 Wilton Road, London SW1V 1BZ, UK.