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!
Conversation
As you comment, let's keep the discussion friendly and respectful to add value to the conversation.
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse varius enim in eros elementum tristique. Duis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere. uis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere.