Sumif across multiple sheets

[gblum]

Hi this topic has been covered a bit including by me asking a similar question with a slightly different purpose. I am trying to perform the equivalent of a sumif across many excel sheets and I'm trying to avoid sumif[sheet1]+sumif[sheet2] for 75-100 sheets. I tried a VBA 3D code and it worked great except it took a full 60 seconds for my workbook to update for every change.

Now on my sample workbook I am trying to sum payroll in columns C:N, representing months of the year, on sheets Fund1, Fund2, and Fund3. The lookup values are in column A on each of those sheets all of the data is in rows 5-8 consisting of names in column A and salary amounts in columns C:N. The catch is there are 7 names in total, four each randomly assigned to each sheet. These seven names are summarized on the summary sheet called Fund Summary. The summary sheet also has all 12 months, but in columns E- P. The seven names are in rows 5:11 on the summary sheet.

I will try to upload the sample table we used before from my last request. It already has a WS table set up and a sumif indirect formula, but for column matching. I left the formula on one row on the Fund Summary tab only in case it helps as a starting point.

I hope someone can help. My boss has discarding my work because of the VBA slow down and gone to a manual sheet with forced numbers and fake projections.

Thanks Gary

 

[NARAYANK991]

Hi Gary ,

How is the data getting into the individual FUND tabs ?

Can you not have all the names that are present on the Summary tab , also available on every individual FUND tab ? If you could do this , it would be a simple matter to use Excel's in-built Consolidate feature.

If you are talking of 75 - 100 tabs , I think it is impossible that anyone can provide a formula solution that will be faster than a VBA solution.

Narayan

 

[gblum]

Hi Narayan,

Thanks for taking the time to look at this.

Most of the data comes from a table of actual payroll data. If you're thinking sumif that table, that would work awesomely, except for future months (currently March, April, May, June) are a projections which are based on a calculation on each sheet, different for each person, and is not based on any prior payroll history.

It is not helpful from a management standpoint to list all names in the same order on each sheet. If we are paying 100 people and Zsuzsa Zamboli is the only one being paid on one of the sheets, there would be a lot of blank space on that individual Fund Report (sheet), lots of scrolling.

I'm working on a sumif-indirect formula that may work. I'll post it when I've tried it.

I've never used the consolidate feature - need to look at that.

Thanks again,
Gary

 

[gblum]

So this is what I came up with and it seems to work when pasted it in E5 on the Fund Summary sheet and dragged across the rest of the cells E511.

=SUMPRODUCT(SUMIF(INDIRECT("'"&WSList&"'!$A$5:$A$8"),$C5,INDIRECT("'"&WSList&"'!C$5:C$8")))

I will now try it on the real workbook. This method should work great except you must maintain the WSlist of tab (sheet) names. Also, I found the last time I used a similar formula that tab names with certain characters like # and maybe - caused the formula to not function.

Thanks,
Gary

 

[PaulF]

I have a dashboard with almost 200 worksheets that are invoices. They have hours of labor for 2 categories, 1-6 ticket numbers, 1-5 server names....

I bring all of that data onto a summary sheet:

Then all of the normal sumif(s), countif(s), etc all make it easy(ier) to work without difficulty of the data being across hundreds of sheets:

This middle step of bringing all of the data to a summary sheet worked, perhaps this counld help you as well *shrug*

Respectfully,
PaulF

 

[gblum]

Thanks Paul. I like what you've done. It's well beyond me. I'm glad my task wasn't that difficult.

FYI, anyone reading my second post, the wagging tongue was supposed to be colonP which converted into an emoticon - too funny.

Gary