calculate total items meeting criteria in range with multiple shee
 

I have several sheets setup from which I need to caluculate: how many of the
cells in a range meet both criteria. It's setup something like the below:


Sheet name"Candace" Sheet name "Sylvia" Sheet name "Wendy"

A B C D E F.... A B C D E F A B C D E F
1 y y n n y y y n n y y n n y y y
y n
2 n y n y y n y n y y y y n y n y
y y


I need to determine the number of "y" in range A1:F1 that are also "y" in
A2:F2
in sheets "Candace", "Sylvia", and "Wendy".

Thanks!

calculate total items meeting criteria in range with multiple shee
 

Download and install the free add-in Morefunc.xll...

http://xcell05.free.fr/morefunc/english/index.htm

Then try...

=SUMPRODUCT(--(THREED('Candace:Wendy'!$A$1:$F$1)="Y"),--(THREED('Candace:
Wendy'!$A$2:$F$2)="Y"))

Otherwise, let A2:A4 contain the sheet names (Candace, Sylvia, and
Wendy), then try...

=SUMPRODUCT(--(T(OFFSET(INDIRECT("'"&$A$2:$A$4&"'!A1:F1"),,COLUM N(INDIREC
T("A1:F1"))-COLUMN(INDIRECT("A1")),,1))="Y"),--(T(OFFSET(INDIRECT("'"&$A$
2:$A$4&"'!A2:F2"),,COLUMN(INDIRECT("A2:F2"))-COLUMN(INDIRECT("A2")),,1))=
"Y"))

Hope this helps!

In article ,
twototango wrote:

I have several sheets setup from which I need to caluculate: how many of the
cells in a range meet both criteria. It's setup something like the below:


Sheet name"Candace" Sheet name "Sylvia" Sheet name "Wendy"

A B C D E F.... A B C D E F A B C D E F
1 y y n n y y y n n y y n n y y y
y n
2 n y n y y n y n y y y y n y n y
y y


I need to determine the number of "y" in range A1:F1 that are also "y" in
A2:F2
in sheets "Candace", "Sylvia", and "Wendy".

Thanks!

calculate total items meeting criteria in range with multiple
 

Thanks!

The first function worked after I remembered to crtl,shift,enter after I was
finished i/o just "enter".

I really appreciate your help!!

"Domenic" wrote:

Download and install the free add-in Morefunc.xll...

http://xcell05.free.fr/morefunc/english/index.htm

Then try...

=SUMPRODUCT(--(THREED('Candace:Wendy'!$A$1:$F$1)="Y"),--(THREED('Candace:
Wendy'!$A$2:$F$2)="Y"))

Otherwise, let A2:A4 contain the sheet names (Candace, Sylvia, and
Wendy), then try...

=SUMPRODUCT(--(T(OFFSET(INDIRECT("'"&$A$2:$A$4&"'!A1:F1"),,COLUM N(INDIREC
T("A1:F1"))-COLUMN(INDIRECT("A1")),,1))="Y"),--(T(OFFSET(INDIRECT("'"&$A$
2:$A$4&"'!A2:F2"),,COLUMN(INDIRECT("A2:F2"))-COLUMN(INDIRECT("A2")),,1))=
"Y"))

Hope this helps!

In article ,
twototango wrote:

I have several sheets setup from which I need to caluculate: how many of the
cells in a range meet both criteria. It's setup something like the below:


Sheet name"Candace" Sheet name "Sylvia" Sheet name "Wendy"

A B C D E F.... A B C D E F A B C D E F
1 y y n n y y y n n y y n n y y y
y n
2 n y n y y n y n y y y y n y n y
y y


I need to determine the number of "y" in range A1:F1 that are also "y" in
A2:F2
in sheets "Candace", "Sylvia", and "Wendy".

Thanks!