• 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.

SumIFS across Multiple Worksheets

M

Mofi454

New Member
I need a way to carry out a sumifs that will loop through multiple worksheets in a workbook.
I have a worksheet named "Summary". This worksheet will list every worksheet that exists in the workbook and will be created on the fly based on a workbook event
I need the Macro to look down Column A, starting at A7 of the Summary tab and find each of the tabs that exist in the workbook then carry out a sumif based on criteria listed in the Summary Tab
I need two different Sumifs calculations

60402
 
Version of excel?

As well, picture doesn't really help us help you. I'd recommend uploading desensitized mock up workbook with some sample row data and result.
 
I'd strongly recommend that you restructure your data.

In current format, it will be very difficult to perform aggregation and analysis dynamically.

Think flat table with additional column to track type of entry. Then you can simply use Pivot table to summarize everything.
 
So I can change the way each of the data tabs are presented but I think I would still need to have a different tab for each order number. I didn't think a pivot table could work across multiple tabs. Or are you saying I should hold all data in the different tabs into one tab. Thanks
 
Yep, that's what I'm saying.

But alternately, you can have separate tabs for each order number. Then load it to PowerQuery using Get & Transform (load from range/table).

Then perform Append query operation and consolidate data into single table (you'll use sheet name as another column Order#).

Then load it to data model, and use pivot table to report on it. I'll see if I can go through your sample in detail tomorrow and do a mock-up workbook.
 
Sorry for late reply. I was bit busy.

I've got bit of question, how should end result look like from your sample? Can you fill data manually to show me what the desired result would be when GBP is picked?

See attached for sample worksheet set up (excluding summary).
 

Attachments

You must log in or register to reply here.
Back
Top