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!