Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 13
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 265
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 13
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
calculate total number of items that meet 2 over multiple sheets twototango Excel Worksheet Functions 5 May 29th 08 07:04 PM
Sum within a date range meeting one other criteria [email protected] Excel Worksheet Functions 3 January 28th 07 05:53 AM
Totals based on meeting multiple criteria JerryS Excel Worksheet Functions 3 January 8th 06 10:35 PM
Top Five selections based on sum of items meeting criteria Joe D Excel Worksheet Functions 2 November 21st 05 12:49 AM
Average of numbers within a range meeting certain criteria opal23k Excel Worksheet Functions 4 August 25th 05 08:51 PM


All times are GMT +1. The time now is 07:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"