PDA

View Full Version : Sleeper: Formula question



malik641
09-06-2005, 11:31 AM
I know this is mostly VBA help, but I was hoping somebody could help me with this.

Okay
I'm trying to SUM multiple worksheets based on a certain criteria. Here is what I have:


=SUMPRODUCT((T(INDIRECT("'"&Employees&"'!C1"))=H1)*(N(INDIRECT("'"&Employees&"'!B2"))=H2)*(N(INDIRECT("'"&Employees&"'!C2"))))

In the sheet with the Formula:
H1=Yes
H2=Date

In each sheet from "Employees"
C1=Yes/No/Text
B2=Date

This formula basically says:
If C1 in each sheet from "Employees" is H1 ("Yes") AND B2 from each sheet from "Employees" is H2 (1-Jan), then sum every C2 in each sheet from "Employees".

"Employees" is a defined name that is a list of all the worksheets' names that I'm interested in for the formula.

This formula works fine, but instead of summing every C2 in each sheet, but I'm trying to make this formula work a little more differently.

I want it to read Row 1 and search for "Yes" (H1). Then I want it to read through column B for 1-Jan (H2). Then take the intersect of that and sum (from a table) of all the sheets.

This is what I came up with:


=SUMPRODUCT((T(INDIRECT("'"&Employees&"'!$1:$1"))=H1)*(N(INDIRECT("'"&Employees&"'!$B:$B"))=H2)*(N(INDIRECT("'"&Employees&"'!C2:F10"))))

Where in the "Employees" sheets:
1:1= Yes or No or Any Text
B:B= Date

Can anybody help? I keep getting zeros...???
Here is an attachment to further understand what I'm looking for.
Thanks in advance!

malik641
09-08-2005, 11:03 AM
Forget it, I'll just make a macro function. This is just TOO complicated.

Zack Barresse
09-08-2005, 11:10 AM
Your problem is your ranges. Sumproduct cannot handle entire columns or rows. If you can find a match for the ranges to be of equal size then you can do this.

malik641
09-08-2005, 11:27 AM
Well the ranges are of equal sizes because every sheet in the "Employees" list is set up exactly the same way. I just think that I'm going to create a macro function instead...I just have to think out my plan on how to approach it for now.

Zack Barresse
09-08-2005, 11:40 AM
Not with this ...


T(INDIRECT("'"&Employees&"'!$1:$1"))=H1)*(N(INDIRECT("'"&Employees&"'!$B:$B")

malik641
09-08-2005, 01:41 PM
Yes, you're right. Not with that.

But with the spreadsheet that I am REALLY dealing with, all the ranges are the same (as well as table setup).

I tried writing a macro Function, but I'm getting a #Value! error. And with a formula someone helped me out with, I'm getting a #Ref! error.

So here is a small example of the workbook I'm dealing with (with different names, and not all of the sheets...just those relavent) so you understand my problem. I deleted NUMEROUS rows just for ease of reading the workbook as well.

Look at the table setup from each employee's sheet. I had to use two tables because one table would have exceeded column IV. This is where I get the issues with the formula as well as the macro function.

In the productivity sheet, you'll see the formula and the macro function used, each with an error (#Ref! and #Value!, respectively). I need to SUM all the values in each sheet from the Employees list (found in Employees sheet) matching the 'Beginning of week' Date and the name found in column A (in the case where I placed the formulas, Hitachi 917).

*Note
For the 'Beginning of Week' date, the lookup reference in each employee's worksheet is in bold above the Total column.

Please take a look and tell me what you think.
Thanks :thumb