Why Does This Formula Return an Error?? Mhz <Mhz.2agtne_1152091205.5395[ at ]excelforum-nospam.com> 05.07.2006 09:16:04 :confused: Hi, what am I doing wrong with the following formula: =countif(sheet1:sheet31!E6:E35,">=1") FOR some reason this formula fails when I use the (sheet1:sheet31) with the column range (E6:E35). Is it possible to include a multiple sheet count and column range in the same Formula? Thanks for any helpful responses.. By the way, the formula works ok just as long as I'm not trying to read multiple sheets.. Please help on this. -- Mhz ------------------------------------------------------------------------ Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35980 View this thread: http://www.excelforum.com/showthread.php?threadid=558346

Re: Why Does This Formula Return an Error?? "Bob Phillips" <bob.NGs[ at ]somewhere.com> 05.07.2006 10:01:32 =SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:31"))&"'!E6:E35"),">=1 ")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mhz" <Mhz.2agtne_1152091205.5395[ at ]excelforum-nospam.com> wrote in message news:Mhz.2agtne_1152091205.5395[ at ]excelforum-nospam.com... [Quoted Text] > > :confused: Hi, what am I doing wrong with the following formula: > > =countif(sheet1:sheet31!E6:E35,">=1") > > FOR some reason this formula fails when I use the (sheet1:sheet31) with > the column range (E6:E35). > > Is it possible to include a multiple sheet count and column range in > the same Formula? Thanks for any helpful responses.. > > By the way, the formula works ok just as long as I'm not trying to read > multiple sheets.. Please help on this. > > > -- > Mhz > ------------------------------------------------------------------------ > Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35980 > View this thread: http://www.excelforum.com/showthread.php?threadid=558346 >

Re: Why Does This Formula Return an Error?? Mhz <Mhz.2agxtb_1152096603.6952[ at ]excelforum-nospam.com> 05.07.2006 10:46:46 Thanks Bob for the fast response, Much appreciated.. I keep getting an Invalid Cell Reference Error with this formula, should I substitute any data in the formula to adapt to my sheet names as I displayed? My sheets are labled (1ST:31ST) eg. 1ST, 2ND, 3RD ...31ST etc.... thanks in advance.. -- Mhz ------------------------------------------------------------------------ Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35980 View this thread: http://www.excelforum.com/showthread.php?threadid=558346

Re: Why Does This Formula Return an Error?? "Bob Phillips" <bob.NGs[ at ]somewhere.com> 05.07.2006 14:10:14 Sorry, I picked up on sheet1:sheet31 as in your post. As there is no way to deduce the sheet names in this format, you will need to store them in M1:M31 and use =SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:M31&"'!E6:E35"),">=1")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mhz" <Mhz.2agxtb_1152096603.6952[ at ]excelforum-nospam.com> wrote in message news:Mhz.2agxtb_1152096603.6952[ at ]excelforum-nospam.com... [Quoted Text] > > Thanks Bob for the fast response, Much appreciated.. > > I keep getting an Invalid Cell Reference Error with this formula, > should I substitute any data in the formula to adapt to my sheet names > as I displayed? My sheets are labled (1ST:31ST) eg. 1ST, 2ND, 3RD > ..31ST etc.... > > thanks in advance.. > > > -- > Mhz > ------------------------------------------------------------------------ > Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35980 > View this thread: http://www.excelforum.com/showthread.php?threadid=558346 >

Re: Why Does This Formula Return an Error?? "Bob Phillips" <bob.NGs[ at ]somewhere.com> 05.07.2006 14:11:44 Following on, if you changed the names to Day1, Day2, etc. you cou;ld then use =SUMPRODUCT(COUNTIF(INDIRECT("'Day"&ROW(INDIRECT("1:31"))&"'!E6:E35"),">=1") ) without defining the names -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mhz" <Mhz.2agxtb_1152096603.6952[ at ]excelforum-nospam.com> wrote in message news:Mhz.2agxtb_1152096603.6952[ at ]excelforum-nospam.com... [Quoted Text] > > Thanks Bob for the fast response, Much appreciated.. > > I keep getting an Invalid Cell Reference Error with this formula, > should I substitute any data in the formula to adapt to my sheet names > as I displayed? My sheets are labled (1ST:31ST) eg. 1ST, 2ND, 3RD > ..31ST etc.... > > thanks in advance.. > > > -- > Mhz > ------------------------------------------------------------------------ > Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35980 > View this thread: http://www.excelforum.com/showthread.php?threadid=558346 >

Re: Why Does This Formula Return an Error?? Mhz <Mhz.2ail2o_1152173407.5751[ at ]excelforum-nospam.com> 06.07.2006 08:01:28 Now I see, Thanks again Bob... Don't want to push my questioning here, but using that same formula how can I modify it to check and count for duplicate values? If Possible... That long formula on my first question in this thread is doing just that, "Finding Duplicates" and counting when they are found. I just didn't want such a long formula to accomplish a small task for each row.. Thanks -- Mhz ------------------------------------------------------------------------ Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35980 View this thread: http://www.excelforum.com/showthread.php?threadid=558346