Countif gone wrong

  • Thread starter Thread starter Isis
  • Start date Start date
I

Isis

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 thi
=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
 
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.
 
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.
 
Back
Top