I have a workbook with multiple worksheets and each worksheet will be identical. I have a summary sheet and I would like to do a SUMIFS across all the worksheets if it meets certain criteria.
On the summary sheet in cell C8 I want to sumifs with the follow criteria.
Worksheets: Criteria Range 1 = Column A, Criteria 1 = "Drafting"
Criteria Range 2 = Column B, Criteria 2 = "External"
Sum Range = Column N
I hope that makes sense. Please see attached example file.
Last edited by keith6292; 05-29-2019 at 11:36 AM.
Attach the workbook here, please.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
The Paperclip icon does not work on this forum, but it would help if you attached a sample Excel workbook.
To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.
SUMIFS doesn't work very well across different sheets, so it is probably better to set up your SUMIFS formulae in the same cells within each sheet, and then on your summary sheet you can just use SUM across those sheets.
Hope this helps.
Pete
You mention SUMIFS but profile states 2003, so I'll assume you're running a later version -- either way, you need to use INDIRECT with a range holding tab references*
=SUMPRODUCT(SUMIFS(INDIRECT("'"&_SheetList&"'!N:N"),INDIRECT("'"&_SheetList&"'!A:A"),"Drafting",INDIRECT("'"&_SheetList&"'!B:B"),"External"))
where _SheetList is a named range containing all tabs to be included within the above calculation
* if your tab names can be derived, logically (e.g. Sheet1 to Sheet100), you can likely create dynamically within the formula itself - e.g. INDIRECT("'Sheet"&ROW($1:$100)&"'!B:B")
edit: FWIW, I agree with PeteUK -- 3D conditional calcs generally perform poorly, so if using intensively consider helper cells, as outlined, a UDF would likely need to be Volatile and thus not overly efficient either.
Last edited by XLent; 05-29-2019 at 10:50 AM.
@Pete_UK, @XLent
Interesting comments re the poor calculation performance of SUMIFS (and 3D constructions in general). Why is this, volatility aside, of course?
And are you saying that an equivalent non-3D construction, e.g.
=SUMIFS(Sheet1!N:N,Sheet1!A:A,"Drafting",Sheet1!B:B,"External")+SUMIFS(Sheet2!N:N,Sheet2!A:A,"Drafting",Sheet2!B:B,"External")+SUMIFS(Sheet3!N:N,Sheet3!A:A,"Drafting",Sheet!B:B,"External")+...
performs better?
Regards
@XOR LX,Originally Posted by XOR LX
On the contrary, Volatility was the sole basis for my comment hence subsequent point that a UDF is unlikely to perform much better (unless passing all precedent ranges, hence 'unlikely').
On an aside, IME SUMIFS doesn't seem to treat the UsedRange/PrecedentRange intersect quite as efficiently as other native functions; seems to fall somewhere between the performance of a pre-2007 function like SUMIF, and the iterative Array calcs like SUMPRODUCT/CSE; for that reason I generally advocate against entire column references with SUMIFS but that's just me... and, when used in conjunction with volatiles etc...
Cheers,
D.O.
Thanks, XLent.
The debate as to the performance of the ...IF(S) family of functions with arbitrarily large ranges (3D or not) is, it would seem, not clear cut. For example, see joeu2004's posts in this thread, in which he is of the opinion that SUMIF/SUMIFS perform equally well whatever the size of the ranges passed, though not, interestingly, COUNTIF/COUNTIFS.
Regards
@XOR_LX, thanks for the link - very interesting; I suspect I'd only noted the degradation on COUNTIFS and jumped to the conclusion that the same would apply to all the IFS.
I'd jumped to a similar conclusion. Interesting that there are such differences in performance between functions within the same 'family'.
Cheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks