Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
can i.. do a COUNT through mutiple worksheets?
i have a workbook that i add workshhets to daily, one worksheet for
everyday. i would like to be able to have a reporting page that would go out to all of the pages included in the workbook and look at 1 column (the same column on all pages) and come back with a count for cells that have a particular text string. *running total* i.e. page '12jan' and page '13jan' both have column J that has one of three entries (ss, swc, np) on my 'reports' page i would like to see the running total of all rows that have "swc" in column J on all pages. since i add sheets everyday i would like to add a wildcard so that the formula on the reports page automaticaly includes the new sheets. *weekly totals* i would like also to break this down by week. or perhaps it would actually be per day. i would like to show "week1" and the next cell show a count of rows that had "swc" for '12jan:17jan'. if this could be self replicating that would be nice..... any thoguhts? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
can i.. do a COUNT through mutiple worksheets?
Here is one way. It doesn't take wildcards like you ask, you have to
maintain a list of all the worksshets in M1:Mn, so any new sheet name must be added here =SUMPRODUCT(COUNTIF(INDIRECT(OFFSET(M1,,,COUNTA(M1 :M1000),1)&"!J1:J1000"),"s wc")) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "ninetynine" wrote in message oups.com... i have a workbook that i add workshhets to daily, one worksheet for everyday. i would like to be able to have a reporting page that would go out to all of the pages included in the workbook and look at 1 column (the same column on all pages) and come back with a count for cells that have a particular text string. *running total* i.e. page '12jan' and page '13jan' both have column J that has one of three entries (ss, swc, np) on my 'reports' page i would like to see the running total of all rows that have "swc" in column J on all pages. since i add sheets everyday i would like to add a wildcard so that the formula on the reports page automaticaly includes the new sheets. *weekly totals* i would like also to break this down by week. or perhaps it would actually be per day. i would like to show "week1" and the next cell show a count of rows that had "swc" for '12jan:17jan'. if this could be self replicating that would be nice..... any thoguhts? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
can i.. do a COUNT through mutiple worksheets?
For the 'weekly totals', try the following..
Let A1 contain your start date, such as 2006/1/12. Let Column B contain the week number, such as 1 for 'Week 1', 2 for 'Week 2', and so on. Then, enter the following formula in C1 and copy down: =SUMPRODUCT(COUNTIF(INDIRECT(TEXT(ROW(INDIRECT($A$ 1+B1*7-7&":"&$A$1+B1*7- 7+5)),"ddmmm")&"!J1:J1000"),"swc")) Hope this helps! In article .com, "ninetynine" wrote: i have a workbook that i add workshhets to daily, one worksheet for everyday. i would like to be able to have a reporting page that would go out to all of the pages included in the workbook and look at 1 column (the same column on all pages) and come back with a count for cells that have a particular text string. *running total* i.e. page '12jan' and page '13jan' both have column J that has one of three entries (ss, swc, np) on my 'reports' page i would like to see the running total of all rows that have "swc" in column J on all pages. since i add sheets everyday i would like to add a wildcard so that the formula on the reports page automaticaly includes the new sheets. *weekly totals* i would like also to break this down by week. or perhaps it would actually be per day. i would like to show "week1" and the next cell show a count of rows that had "swc" for '12jan:17jan'. if this could be self replicating that would be nice..... any thoguhts? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3D Count in all worksheets | Excel Worksheet Functions | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
Count of Worksheets | Excel Discussion (Misc queries) | |||
How to count # of worksheets? | Excel Discussion (Misc queries) | |||
Count the number of worksheets in a workbook | Excel Discussion (Misc queries) |