• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

sumif is such a hog

E

Eric M

Member
=IF(C2="Actuals",IF(W2="include",SUMIFS($P$1:$P$62813,$B$1:$B$62813,AA2,$V$1:$V$62813,V2),""),"")

So, I nested the sumifs in two If statements that run on helper columns to try and reduce the number of times the sumif actually calculates. Either way on 62k lines sumifs brings excel to its knees.

Anyone got faster alternatives?

In English formula is =If( Budget ID = Actuals, If ( IorE = Include, Sumifs ( Balance sum range, Date range, Last Years Date criteria, Unique ID range, Unique Id criteria, blank), blank)
 
yes, use a PivotTable. http://dailydoseofexcel.com/archives/2014/08/04/formulas-pah/

You can also do something called Range Slicing, if you can alphabetically sort each column of your data.

And you can also use Excel's Database functions. (They start with D e.g. DSUM)

Or you can use the Advanced Filter to extract the data you need, then sum that.

I have a great sample file somewhere that shows all of these in action, but I need to amend it so it is small enough to upload.

But by far my preferred method is the PivotTable. Here's another great article that touches on why PivotTables are often a much better alternative to complicated, resource intensive formulas:
https://exceljet.net/blog/pivot-tables-you-havent-seen-before
 
Last edited:
A pivot table would be fantastic as the formula is necessary only to feed into a pivot table. The problem is that I am displaying "Balance" as a value field in the pivot table by Month, the sumif formula in question returns the "Balance" for the same month of the previous year. I can do that in formula, I am pretty certain i can't do that in the pivot table without feeding it from a formula while still having the current month balance and prior year balance showing on the same line.

I'll take a look at using if and dsum in the morning. Thanks for the suggestion.
 
My understanding after looking into DSum is that won't work since I am trying to grab the balance for the same date of the prior year from a differen't row in the same column and D functions aren't meant to evaluate individual rows in a column.

I also tried using sumproduct and a few too many neseted if's with a vlookup at the end and neither of those methods are very fast.
 
My email notification says another person responded but I don't see any new posts. Does that mean the comment was deleted?
 
Hi Eric ,

Is it possible for you to upload a workbook which has the the data layout as your working file , and possibly has some sample data ?

A general question may or may not get specific answers ; a specific question will in all probability get one.

Narayan

P.S. : As far as your latest question is concerned , I have no idea whether someone posted a comment and then deleted it ; some users have the facility to permanently delete their posts , and what you apprehend may have happened.
 
You must log in or register to reply here.
Back
Top