SUMIF and list
Hi,
I got this sum of SUMIFS:
As you might guess, from B3 down there's a list of sheetnames and I want to do a SUMIF for the sheets in that list. Right now the only way I could come up with is what's written above. There must be a shorter way, but I haven't got the slightest idea what code I need to use.
Please Login or Register to view this content.
Thanks in advance for the help!
Hi, welcome to the forum
try this approach.
1. Make a range name for all the sheet names you have in B (I used SumTabs)
2. Use this formula, adjusted to suite your ranges...
=SUMPRODUCT(SUMIF(INDIRECT("'"&SumTabs&"'!A6:A1000"),$E5,INDIRECT("'"&Sumtabs&"'!G6:G1000")))
Try and avoid using entire columns/rows with INDIRECT(), it will start slowing your file down
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thanks!
I tried it:
It recognizes the datatabs-table, but as result I'm getting a #REF! now. The sum of SUMIF's from the first post does work and it basically does the same.. Any idea why SUMPRODUCT doesn't work?Please Login or Register to view this content.
Last edited by sklinderman; 10-23-2014 at 04:37 PM.
I found the problem: some cells in the datatabs-range haven't got a value, which results in Excel searching for sheets that don't have a name, which don't exist, which results in a reference-error.
So how do I edit this so it checks if the cells have a value? I tried using SUMIFS instead of SUMIF to add a check for value, but I can't get it right.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks