Sumif help with multiple tabs

[marykayejn]

I'm trying to add wages for employees from different tabs in my workbook. I want to be able to delete employees who don't have wages in certain programs, so I need to say, "if Jessica has wages in this cell add it". I just can't get this formula to work. Should I not be using the SUMIF formula?

[Cutter]

Try:

=SUM('BSS-CSP:PSYCH'!C4)

[marykayejn]

Cutter - That would work if I didn't want to delete names from one worksheet, but not the others. I want this formula in all cells to total all wages and hours from all worksheets. But I don't want unnecessary employees in each worksheet so the formula has to look at the employee name.

[Cutter]

OK, misunderstood what you were asking.
Are there going to be other sheets to total or will there only be the 3 that are currently present?

[marykayejn]

There will be others. And, occasionally a sheet will be removed.

[Cutter]

That's what I was afraid of - I have put out the call for the experts to give you a hand with this one.

[DonkeyOte]

3D conditional Calculations are inefficient and volatile.

You would be best served replicating whatever conditional calcs you need on each sheet individually in identical tables and using standard 3d functions (like SUM etc) to aggregate on Summary.
That or creating a singular consolidated data sheet.

To elaborate regards inefficient approach - assume you want to:

Sum C1:C1000 on Sheets: Apple, Banana, Carrot, Dog & Egg where name in A1:A1000 is "Bob"

First - create a list that holds all sheet names - let's say this is in Z1:Z5 on your Summary Sheet, the function would be:

Please Login or Register  to view this content.

It's not the right way to go - IMO at any rate.

Have you considered attempting a Multi Consolidation Pivot Table and/or using VBA to consolidate the various sheets into one single data set for sake of analysis ?

[Cutter]

An easy alternative for you would be to keep the formulas you are already using and, instead of deleting the employees from the sheets, just hide the rows that contain those employees.

But you will run into trouble if you delete either the first or last of those named sheets with your current formulas. You should insert 2 extra sheets, one named FIRST, the other named LAST. You then hide those sheets and insert your sheets to be totaled between those 2 hidden sheets. That way your summing formulas won't be affected. Adjust your formulas to read FIRST:LAST!

[marykayejn]

DonkeyOte,

I've seen this code before and tried it but couldn't get it to work. I just come back with #REF. I'm attaching my latest attempt.

I've never used (or really understand) Pivot Tables or VBA so it would be a whole new learning curve . . . why do you say IFSUM is inefficient?

[DonkeyOte]

I've seen this code before and tried it but couldn't get it to work. I just come back with #REF.

Your sheet listing can only contain valid sheets - your list is set as M1:M5 with first & last rows being empty - should be M2:M4 (returns 10)

why do you say IFSUM is inefficient?

I presume you mean the SUMPRODUCT etc...

SUMPRODUCT is an iterative formula and is slower to process than "normal" formula (it's no quicker than an Array)

INDIRECT is Volatile

The two combined are generally bad news.

For more info. on Volatility see the link in my signature.

[marykayejn]

Cutter, I hadn't thought of just hiding the rows . . .

[DonkeyOte]

Cutter, I hadn't thought of just hiding the rows . . .

and FWIW I agree - do whatever you can to avoid the need for complex & inefficient approaches

[marykayejn]

Thanks so much guys! The INDIRECT works and so does just hiding the rows. Now I have a choice!