tag:blogger.com,1999:blog-1869924468172210809.post2741497240015658241..comments2024-03-22T10:09:26.517+00:00Comments on Not Just Numbers: Excel Tip: Adding up columns based on multiple criteria (the SUMIFS function)Glen Feechanhttp://www.blogger.com/profile/12461985809302036952noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-1869924468172210809.post-86785571578560291572017-04-16T08:30:50.973+01:002017-04-16T08:30:50.973+01:00Thank u :)Thank u :)KARAOKE DUONG PHOhttps://www.blogger.com/profile/08498165369408288965noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-21835254896680662182017-01-19T07:46:20.540+00:002017-01-19T07:46:20.540+00:00I really like Sumifs and countifs. It helped a lot...I really like Sumifs and countifs. It helped a lot.Jio TVhttp://www.jiotvapp.com/noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-34565744929634872032016-05-10T18:59:11.675+01:002016-05-10T18:59:11.675+01:00Thanks Paul. I probably should have covered wildca...Thanks Paul. I probably should have covered wildcards!Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-51877300990514267782016-05-10T18:58:29.273+01:002016-05-10T18:58:29.273+01:00Me too Jim! I nearly mentioned both PivotTables an...Me too Jim! I nearly mentioned both PivotTables and helper columns but thought the post was already quite long and wanted to stick to the priority of explaining SUMIFS.<br /><br />I tend to go to PivotTables as a first port of call for this kind of thing, but they are not appropriate in every situation - and this post was about SUMIFS.<br /><br />In practice, I would typically use a helper column for the month, i.e. =MONTH(A2), but I thought it would be better to explain how to use < and > with SUMIFS.<br /><br />Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-44587740270896281532016-05-10T12:16:37.151+01:002016-05-10T12:16:37.151+01:00yes, that works
still learning after all these yea...yes, that works<br />still learning after all these years<br /><br />last para still applies (even in a simple example)<br /><br />jimAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-23106127379120069362016-05-10T12:06:00.027+01:002016-05-10T12:06:00.027+01:00Or use wildcards in the criteria, so in Jim's ...Or use wildcards in the criteria, so in Jim's example you could write<br /><br />=SUMIFS(D2:D21,C2:C21,"M*")Paulnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-61627008262014474532016-05-10T10:36:20.306+01:002016-05-10T10:36:20.306+01:00If, as a convoluted example, you want all the sale...If, as a convoluted example, you want all the sales for Salespersons beginning with M, putting a LEFT function in will not work (or any function in the criteria range)<br />For that, you need to add a helper column or use SUMPRODUCT to produce a nightmare like this:<br /> =SUMPRODUCT($D$2:$D$21*(LEFT($C$2:$C$21)="M"))<br /><br />A much better solution for nearly all these situations is to use a Pivot Table (even better if you format your data as a Table first - which may even do the job by itself)<br /><br />jimAnonymousnoreply@blogger.com