LinkBack URL
About LinkBacks
I don't see why I would need to use an array over a SUMIFS, can someone shed some light on why the Array is considered better and when I would use it?
Thanks.
I don't see why I would need to use an array over a SUMIFS, can someone shed some light on why the Array is considered better and when I would use it?
Thanks.
A few reasons, the most obvious is that you cannot use a range expression in SUMIF/SUMIFS. For example, say you want to test the year, you can't with SUMIFS. In some instances, you need to outsort some conditions that SUMIF/SUMIFS choke on, you need an array formula to do that.
If you can, you should use SUMIF/SUMIFS, but it is not always possible with helper columns.
SUMIFS also won't work if the source data is in a separate closed workbook.
Remember what the dormouse said
Feed your head
Some methods may be better for certain situations while other methods could be better for other situations. An array is simply a collection of objects, whereas SUMIFS is a function. Could you give an example of a situation where you would use one or the other?
Is your code running too slowly?
Does your workbook or database have a bunch of duplicate pieces of data?
Have a look at this article to learn the best ways to set up your projects.
It will save both time and effort in the long run!
Dave
You mean dates dont work with SUMIFS?
lets say you have five pieces of data, - COLOR - Amount - Date - City - Age - couldn't you mix and match this with SUMIFS?
It depends on what exactly the criteria are.... so if you are looking for a specific date or a specific date range, then yes you can use SUMIFS.
e.g.
=SUMIFS(B:B,A:A,"Red",C:C,DATE(2011,1,1),D:D,"Toronto",E:E,">25")
will check all those criteria to sum column B.. .where date is specifically Jan 1, 2011
or
=SUMIFS(B:B,A:A,"Red",C:C,">="&DATE(2011,1,1),C:C,"<="&DATE(2011,12,31),D:D,"Toronto",E:E,">25")
will check all the criteria and for Year 2011 (or between Jan 1 and Dec 31, 2011) and sum column B.
What Bob was saying is you can't check something like criteria is Data must be a specific month.
e.g.
=SUMIFS(B:B,A:A,"Red",MONTH(C:C),12,D:D,"Toronto",E:E,">25")
This won't work to check if the month of column C is Month 12 (December). But, again, if all the dates were in a specific year, then you can use something similar to my previous example.
i.e.
=SUMIFS(B:B,A:A,"Red",C:C,">="&DATE(2011,12,1),C:C,"<="&DATE(2011,12,31),D:D,"Toronto",E:E,">25")
to get all the December, 2011 dates included along with the other criteria.
Sometimes it takes some creativity if you insist on using SUMIFS, but sometimes, Sumproduct is an easier way out... especially for smaller ranges....
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks