Difficulties with COUNTIF.

[Martin M]

Hello. I'm trying to add the number of times a certain piece of text occurs
in a column. I can use COUNTIF when I'm only looking at a sequential column
of cells eg B2:B9, but not when they're not sequential eg B2, B5, B8:B11, B13
etc. Excel just won't let me do and I can't work out why! If anyone has any
idea how to sort this, it would make doing the rotas for work much easier.

Many Thanks, Martin

[bj]

You probably have two criteria for selection. one to select which cells to
look at and one to decide whether to count that cell. If this is the case
sumproduct will work
=sumproduct(--(range criteria one),--(range criteria two))

"Martin M" wrote:

> Hello. I'm trying to add the number of times a certain piece of text occurs
> in a column. I can use COUNTIF when I'm only looking at a sequential column
> of cells eg B2:B9, but not when they're not sequential eg B2, B5, B8:B11, B13
> etc. Excel just won't let me do and I can't work out why! If anyone has any
> idea how to sort this, it would make doing the rotas for work much easier.
>
> Many Thanks, Martin

[Martin M]

Thanks bj.

I've had a go with sumproduct, but without any success. Partly it's cos I'm
not sure how to properly interpret what you wrote, but also because I think
that sumproduct works with numbers rather than text (although I may well be
completely wrong about that).

I'm going to post another message with a more informative subject heading
and see if I get anywhere with that.

Thanks for your prompt help anyway, Martin.

"bj" wrote:

> You probably have two criteria for selection. one to select which cells to
> look at and one to decide whether to count that cell. If this is the case
> sumproduct will work
> =sumproduct(--(range criteria one),--(range criteria two))
>
> "Martin M" wrote:
>
> > Hello. I'm trying to add the number of times a certain piece of text occurs
> > in a column. I can use COUNTIF when I'm only looking at a sequential column
> > of cells eg B2:B9, but not when they're not sequential eg B2, B5, B8:B11, B13
> > etc. Excel just won't let me do and I can't work out why! If anyone has any
> > idea how to sort this, it would make doing the rotas for work much easier.
> >
> > Many Thanks, Martin

[bj]

sumproduct does work with text.
do you have a criteria which selects what cells you want to look at to
decide if you want to count it.?

"Martin M" wrote:

> Thanks bj.
>
> I've had a go with sumproduct, but without any success. Partly it's cos I'm
> not sure how to properly interpret what you wrote, but also because I think
> that sumproduct works with numbers rather than text (although I may well be
> completely wrong about that).
>
> I'm going to post another message with a more informative subject heading
> and see if I get anywhere with that.
>
> Thanks for your prompt help anyway, Martin.
>
> "bj" wrote:
>
> > You probably have two criteria for selection. one to select which cells to
> > look at and one to decide whether to count that cell. If this is the case
> > sumproduct will work
> > =sumproduct(--(range criteria one),--(range criteria two))
> >
> > "Martin M" wrote:
> >
> > > Hello. I'm trying to add the number of times a certain piece of text occurs
> > > in a column. I can use COUNTIF when I'm only looking at a sequential column
> > > of cells eg B2:B9, but not when they're not sequential eg B2, B5, B8:B11, B13
> > > etc. Excel just won't let me do and I can't work out why! If anyone has any
> > > idea how to sort this, it would make doing the rotas for work much easier.
> > >
> > > Many Thanks, Martin

[Martin M]

Hi bj. The formula I've been using is

=COUNTIF(B2,B4,B7:B11,B14,"E")

Excel then tells me that I've used too many arguements, despite that style
of formula working for SUM to add up numbers.

I hope that answers your question and that'll help you be able to answer my
original question

Thanks again, Martin

"bj" wrote:

> sumproduct does work with text.
> do you have a criteria which selects what cells you want to look at to
> decide if you want to count it.?
>
> "Martin M" wrote:
>
> > Thanks bj.
> >
> > I've had a go with sumproduct, but without any success. Partly it's cos I'm
> > not sure how to properly interpret what you wrote, but also because I think
> > that sumproduct works with numbers rather than text (although I may well be
> > completely wrong about that).
> >
> > I'm going to post another message with a more informative subject heading
> > and see if I get anywhere with that.
> >
> > Thanks for your prompt help anyway, Martin.
> >
> > "bj" wrote:
> >
> > > You probably have two criteria for selection. one to select which cells to
> > > look at and one to decide whether to count that cell. If this is the case
> > > sumproduct will work
> > > =sumproduct(--(range criteria one),--(range criteria two))
> > >
> > > "Martin M" wrote:
> > >
> > > > Hello. I'm trying to add the number of times a certain piece of text occurs
> > > > in a column. I can use COUNTIF when I'm only looking at a sequential column
> > > > of cells eg B2:B9, but not when they're not sequential eg B2, B5, B8:B11, B13
> > > > etc. Excel just won't let me do and I can't work out why! If anyone has any
> > > > idea how to sort this, it would make doing the rotas for work much easier.
> > > >
> > > > Many Thanks, Martin

[Martin M]

Hi again bj. I've managed to work out how to do what I want to do. I've used
=SUM(countif(b4,"e"),countif(b7:b10,"e"),countif(b13,"e")) etc. A bit long
winded but it works.

Again, cheers for your help,

Martin



"bj" wrote:

> sumproduct does work with text.
> do you have a criteria which selects what cells you want to look at to
> decide if you want to count it.?
>
> "Martin M" wrote:
>
> > Thanks bj.
> >
> > I've had a go with sumproduct, but without any success. Partly it's cos I'm
> > not sure how to properly interpret what you wrote, but also because I think
> > that sumproduct works with numbers rather than text (although I may well be
> > completely wrong about that).
> >
> > I'm going to post another message with a more informative subject heading
> > and see if I get anywhere with that.
> >
> > Thanks for your prompt help anyway, Martin.
> >
> > "bj" wrote:
> >
> > > You probably have two criteria for selection. one to select which cells to
> > > look at and one to decide whether to count that cell. If this is the case
> > > sumproduct will work
> > > =sumproduct(--(range criteria one),--(range criteria two))
> > >
> > > "Martin M" wrote:
> > >
> > > > Hello. I'm trying to add the number of times a certain piece of text occurs
> > > > in a column. I can use COUNTIF when I'm only looking at a sequential column
> > > > of cells eg B2:B9, but not when they're not sequential eg B2, B5, B8:B11, B13
> > > > etc. Excel just won't let me do and I can't work out why! If anyone has any
> > > > idea how to sort this, it would make doing the rotas for work much easier.
> > > >
> > > > Many Thanks, Martin