Prev: Limit Cell Content
Next: Converting Excel to HTML
From: Isis on 14 May 2010 03:43 Hi, A B C D E 2000BC DEF DEF DEF 2000BC ABC 2000BC DEF 2000BC DEF 2000BC 2000BC 2000BC 2000BC 2000BC ABC 2000BC DEF DEF DEF This normally works but Im having a problem with it now. My formula looks like this =COUNTIF(Sheet3!A:A,"=2000BC")+COUNTIF(Sheet3!B:B,"=2000BC")+COUNTIF(Sheet3!C:C,"=2000BC")+COUNTIF(Sheet3!D:D,"=2000BC")+COUNTIF(Sheet3!E:E,"=2000BC") The column range is a lot of lines ie reason why I use column:column, the last countif was added & it returns answer as 0 instead of adding 2 moreif I change the formula to only read =COUNTIF(Sheet3!E:E,"=2000BC") it gives me a 0. Is there an alternate formula or is there an error with column thats not calculating? thx
From: Rick Rothstein on 14 May 2010 04:14 COUNTIF works fine and when I try it with the data you posted, this formula... =COUNTIF(Sheet3!E:E,"=2000BC") returns 2, as it should. I don't know why you are getting 0, so I can't help you with that part; however, I thought you might be interested in knowing that this formula... =COUNTIF(Sheet3!A:A,"=2000BC")+COUNTIF(Sheet3!B:B,"=2000BC")+COUNTIF(Sheet3!C:C,"=2000BC")+COUNTIF(Sheet3!D:D,"=2000BC")+COUNTIF(Sheet3!E:E,"=2000BC") can be replaced with this much shorter one... =COUNTIF(Sheet3!A:E,"2000BC") Notice that you don't need the equal sign in front of the string constant that you are checking for.  Rick (MVP  Excel) "Isis" <Isis(a)discussions.microsoft.com> wrote in message news:194FFEBE27E04E9387670B679A289F52(a)microsoft.com... > Hi, > > A B C D E > 2000BC DEF DEF DEF 2000BC > ABC 2000BC DEF 2000BC DEF > 2000BC 2000BC 2000BC 2000BC 2000BC > ABC 2000BC DEF DEF DEF > > This normally works but Im having a problem with it now. My formula > looks like this > =COUNTIF(Sheet3!A:A,"=2000BC")+COUNTIF(Sheet3!B:B,"=2000BC")+COUNTIF(Sheet3!C:C,"=2000BC")+COUNTIF(Sheet3!D:D,"=2000BC")+COUNTIF(Sheet3!E:E,"=2000BC") > The column range is a lot of lines ie reason why I use column:column, the > last countif was added & it returns answer as 0 instead of adding 2 > moreif I > change the formula to only read =COUNTIF(Sheet3!E:E,"=2000BC") it gives > me a > 0. > > Is there an alternate formula or is there an error with column thats not > calculating? > > thx > >
From: Mike H on 14 May 2010 04:19 Hi, Your formula works perfectly for me but can be simplified to =COUNTIF(Sheet3!A:E,"2000BC") If Col E is returning zero then you may have some rogue spaces so try =COUNTIF(Sheet3!A:E,"*2000BC*")  Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Isis" wrote: > Hi, > > A B C D E > 2000BC DEF DEF DEF 2000BC > ABC 2000BC DEF 2000BC DEF > 2000BC 2000BC 2000BC 2000BC 2000BC > ABC 2000BC DEF DEF DEF > > This normally works but Im having a problem with it now. My formula > looks like this > =COUNTIF(Sheet3!A:A,"=2000BC")+COUNTIF(Sheet3!B:B,"=2000BC")+COUNTIF(Sheet3!C:C,"=2000BC")+COUNTIF(Sheet3!D:D,"=2000BC")+COUNTIF(Sheet3!E:E,"=2000BC") > The column range is a lot of lines ie reason why I use column:column, the > last countif was added & it returns answer as 0 instead of adding 2 moreif I > change the formula to only read =COUNTIF(Sheet3!E:E,"=2000BC") it gives me a > 0. > > Is there an alternate formula or is there an error with column thats not > calculating? > > thx > >

Pages: 1 Prev: Limit Cell Content Next: Converting Excel to HTML 