Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate total number of items that meet 2 over multiple sheets | Excel Worksheet Functions | |||
Sum within a date range meeting one other criteria | Excel Worksheet Functions | |||
Totals based on meeting multiple criteria | Excel Worksheet Functions | |||
Top Five selections based on sum of items meeting criteria | Excel Worksheet Functions | |||
Average of numbers within a range meeting certain criteria | Excel Worksheet Functions |