Results 1 to 2 of 2
2006-03-13, 06:19 #1
- Join Date
- Apr 2005
- Thanked 0 Times in 0 Posts
Countif on Multiple Worksheets (Excel 2000)
I have a workbook containing multiple worksheets. These worksheets contain information about many projects that run in my organization. These sheets are arranged in the names of individuals handling these projects and have the same format. There is a cell in all the worksheets that contains information whether the projects are complying to a give target or not. (100% or not) I am required to compute the count of all projects in the work book and report the total count of all projects that comply to the give target.
In a different worksheet for consolidating the information, I am using the formula =countif(first sheet : Last sheet ! Range,100%). This formula is resulting in #Value! error. Can you pls. help me.
2006-03-13, 07:17 #2
- Join Date
- Mar 2002
- Thanked 28 Times in 28 Posts
Re: Countif on Multiple Worksheets (Excel 2000)
Excel does not support 3D references (across multiple sheets) in COUNTIF, SUMIF and SUMPRODUCT. You can find a set of custom functions that do in this newsletter - see the article by Myrna Larson and David Hager at the end.
=CountIf3D("first sheet:last sheet!A1:A11",100%)
Note that the reference to the 3D range is a string delimited by quotes.