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!
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!