tag:blogger.com,1999:blog-1869924468172210809.post6864098336403789244..comments2019-08-21T05:21:38.090+01:00Comments on Not Just Numbers: Excel Tip: An introduction to SUMPRODUCT and why you should learn itGlen Feechanhttp://www.blogger.com/profile/12461985809302036952noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-1869924468172210809.post-5309899114182910152017-09-20T02:05:16.154+01:002017-09-20T02:05:16.154+01:00Nice article SUMPRODUCT rules!
SUMIF + COUNTIF + ...Nice article SUMPRODUCT rules!<br /><br />SUMIF + COUNTIF + SUMIFS + COUNTIFS do not work on closed files - SUMPRODUCT does.<br /><br />SUMPRODUCT also handles leading zeroes in codes correctly - the others don't.<br /><br />The others can't use functions within their brackets SUMPRODUCT can and that is its real super power.<br /><br />eg this sums every second row<br />=SUMPRODUCT((MOD(ROW($A$2:$A$9),2)=1)*($A$2:$A$9))<br /><br />I use the * between the brackets as this is easier to type and explain since when you multiply by true it acts as one and false acts as zero. (It is slightly slower to use * calculation wise)<br /><br />Regards<br /><br />NealeNeale Blackwoodhttps://a4accounting.com.au/noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-79364041689839038872017-09-03T14:56:54.686+01:002017-09-03T14:56:54.686+01:00If you use named ranges then SUMPRODUCT becomes se...If you use named ranges then SUMPRODUCT becomes self-documenting. Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-84086351317253146902017-08-26T00:32:29.579+01:002017-08-26T00:32:29.579+01:00Downside is that SUMPRODUCT is slower in calculati...Downside is that SUMPRODUCT is slower in calculation than SUMIF and COUNTIF, most notable in large data setsSaleemnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-70478807962139526042017-08-21T08:46:28.705+01:002017-08-21T08:46:28.705+01:00Good Point, I only discovered the need for -- when...Good Point, I only discovered the need for -- when I couldn't get this to work for a simple count (on reflection, COUNTIF probably would have done the trick)<br />I used to use this method frequently but then Tables and Pivots came out, and SUMIFS, COUNTIFS and AVERAGEIFS too<br />But, as you say, sometimes only the flexibility of SUMPRODUCT will do<br /><br />jimAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-80517534277602977082017-08-21T06:15:45.731+01:002017-08-21T06:15:45.731+01:00Thanks, good point Jim. I was erring on the side o...Thanks, good point Jim. I was erring on the side of caution, as it will always work with the --, rather than having to learn when you can get away without using it. Also, trying to keep an introduction to the function as simple as I can :)Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-4641806560084295742017-08-20T23:47:25.995+01:002017-08-20T23:47:25.995+01:00Great article again, Glen; well worth the wait
If ...Great article again, Glen; well worth the wait<br />If you're performing further maths on a logical value, you don't need the "--"<br />So SUMPRODUCT((A2:A8="North"),(B2:B8="A"),C2:C8) would do the same job and look slightly less cumbersome<br /><br />jimAnonymousnoreply@blogger.com