Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting rows from columns on multiple worksheets?
I am looking for a formula to count instances, from multiple sheets, where
criteria in one column and another column occur. For example I have 4 worksheets and I amd looking for all instances where column A in all books =Suzie Smith AND where column C in all books = Tuesday. Any ideas? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting rows from columns on multiple worksheets?
try
=sumproduct(--('all books'!A1:A1000="Suzie Smith"),--(C1:C1000="Tuesday")) the --( changes the logical true false to a numeric 1,0. you cannot use (before 2007) the shorthand for the full column A:A is a non-no. "The BriGuy" wrote: I am looking for a formula to count instances, from multiple sheets, where criteria in one column and another column occur. For example I have 4 worksheets and I amd looking for all instances where column A in all books =Suzie Smith AND where column C in all books = Tuesday. Any ideas? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting rows from columns on multiple worksheets?
Assume you have 3 identically structured source sheets named as simply: x, y, z
In your summary sheet, List the 3 sheetnames in A3 down, ie: x, y, z Input the dual criteria in B1:B2, ie: Suzie Smith, Tuesday Then place in B3: =SUMPRODUCT((INDIRECT("'"&$A3&"'!A2:A100")=B$1)*(( INDIRECT("'"&$A3&"'!C2:C100")=B$2))) Copy B3 down to return the counts from each sheet. Then you could just total up the counts in say, B6: =SUM(B3:B5) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "The BriGuy" wrote: I am looking for a formula to count instances, from multiple sheets, where criteria in one column and another column occur. For example I have 4 worksheets and I amd looking for all instances where column A in all books =Suzie Smith AND where column C in all books = Tuesday. Any ideas? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofill counting up Columns and Rows! | Excel Worksheet Functions | |||
Counting rows based on multiple columns | Excel Worksheet Functions | |||
counting rows across multiple worksheets | Excel Discussion (Misc queries) | |||
Counting rows of blanks across certain columns | New Users to Excel | |||
Counting rows with 3 columns | Excel Worksheet Functions |