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 more-if 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:194FFEBE-27E0-4E93-8767-0B679A289F52(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
> more-if 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 more-if 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