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

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

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

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

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

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(b 13,"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