Question regarding the use of SUM in relation to text cells
 

I have a worksheet with 3 columns of cells, each cell with a name and address
in it. Down the left hand side there are area categories, like London or
Leeds.
I want to have a total at the bottom that displays the number of cells for
each area.
For example, it says Leeds on the left, then there are two names in column
a, 1 in column b, and three in column c.
I need a formula that I can put in a cell below this that will total the
cells and display 6.

anyone know if this is possible?

Question regarding the use of SUM in relation to text cells
 

Try this

=SUMPRODUCT((A2:A200="Leeds")*(B2:D200<""))

--
__________________________________
HTH

Bob

"Megglesface" wrote in message
...
I have a worksheet with 3 columns of cells, each cell with a name and
address
in it. Down the left hand side there are area categories, like London or
Leeds.
I want to have a total at the bottom that displays the number of cells for
each area.
For example, it says Leeds on the left, then there are two names in column
a, 1 in column b, and three in column c.
I need a formula that I can put in a cell below this that will total the
cells and display 6.

anyone know if this is possible?

Question regarding the use of SUM in relation to text cells
 

OK thanks, i tried that but it just displayed a 0. :-(

"Bob Phillips" wrote:

Try this

=SUMPRODUCT((A2:A200="Leeds")*(B2:D200<""))

--
__________________________________
HTH

Bob

"Megglesface" wrote in message
...
I have a worksheet with 3 columns of cells, each cell with a name and
address
in it. Down the left hand side there are area categories, like London or
Leeds.
I want to have a total at the bottom that displays the number of cells for
each area.
For example, it says Leeds on the left, then there are two names in column
a, 1 in column b, and three in column c.
I need a formula that I can put in a cell below this that will total the
cells and display 6.

anyone know if this is possible?

Question regarding the use of SUM in relation to text cells
 

Actually just thought, instead of having the identifier as 'leeds' would it
possible to have as the colour of the text? some of the cells are written in
red and some are blue, so could that formula add up the number of red cells
for example?

"Megglesface" wrote:

I have a worksheet with 3 columns of cells, each cell with a name and address
in it. Down the left hand side there are area categories, like London or
Leeds.
I want to have a total at the bottom that displays the number of cells for
each area.
For example, it says Leeds on the left, then there are two names in column
a, 1 in column b, and three in column c.
I need a formula that I can put in a cell below this that will total the
cells and display 6.

anyone know if this is possible?

Question regarding the use of SUM in relation to text cells
 

See http://xldynamic.com/source/xld.ColourCounter.html

--
__________________________________
HTH

Bob

"Megglesface" wrote in message
...
Actually just thought, instead of having the identifier as 'leeds' would
it
possible to have as the colour of the text? some of the cells are written
in
red and some are blue, so could that formula add up the number of red
cells
for example?

"Megglesface" wrote:

I have a worksheet with 3 columns of cells, each cell with a name and
address
in it. Down the left hand side there are area categories, like London or
Leeds.
I want to have a total at the bottom that displays the number of cells
for
each area.
For example, it says Leeds on the left, then there are two names in
column
a, 1 in column b, and three in column c.
I need a formula that I can put in a cell below this that will total the
cells and display 6.

anyone know if this is possible?

Question regarding the use of SUM in relation to text cells
 

Then you have either omitted something or not been clear.


--
__________________________________
HTH

Bob

"Megglesface" wrote in message
...
OK thanks, i tried that but it just displayed a 0. :-(

"Bob Phillips" wrote:

Try this

=SUMPRODUCT((A2:A200="Leeds")*(B2:D200<""))

--
__________________________________
HTH

Bob

"Megglesface" wrote in message
...
I have a worksheet with 3 columns of cells, each cell with a name and
address
in it. Down the left hand side there are area categories, like London
or
Leeds.
I want to have a total at the bottom that displays the number of cells
for
each area.
For example, it says Leeds on the left, then there are two names in
column
a, 1 in column b, and three in column c.
I need a formula that I can put in a cell below this that will total
the
cells and display 6.

anyone know if this is possible?

Question regarding the use of SUM in relation to text cells
 

thanks i will have a look at that

"Bob Phillips" wrote:

See http://xldynamic.com/source/xld.ColourCounter.html

--
__________________________________
HTH

Bob

"Megglesface" wrote in message
...
Actually just thought, instead of having the identifier as 'leeds' would
it
possible to have as the colour of the text? some of the cells are written
in
red and some are blue, so could that formula add up the number of red
cells
for example?

"Megglesface" wrote:

I have a worksheet with 3 columns of cells, each cell with a name and
address
in it. Down the left hand side there are area categories, like London or
Leeds.
I want to have a total at the bottom that displays the number of cells
for
each area.
For example, it says Leeds on the left, then there are two names in
column
a, 1 in column b, and three in column c.
I need a formula that I can put in a cell below this that will total the
cells and display 6.

anyone know if this is possible?