please try this

First of all, i hope this is easy.

Is it possible *?* to have a subtotal() formula in a column that can count
the cells like;

Formula 1) count the visible cells in a column which start with a text "C".

Formula 2) count the visible cells in a column which start with a text "C" &
next number(s) is 2 or more. (e.g. if cell contains text like "C1df",
subtotal will not count, if the cell contains text like "C2dfg" or "C10jky"
or "C6KL", then subtotal will consider to count this.).

Please try to help me on the above subject.

--
regards,

This won't be possible with Subtotal. There is no "SubtotalIf" function in
Excel.

The best solution is likely Sumproduct. In this function, you can check for
a starting "C", as well as the filter conditions that create the visitble
cells.

Your second request also has difficulties because you want to consider 10
greater than 2, which is true if it's a number, but not when it's text. The
easiest solution would be to create a helper column with the number in it.
Then it's easy to compare.

Post back if you need more help.

Regards,
Fred.

"driller" wrote in message
...
First of all, i hope this is easy.

Is it possible *?* to have a subtotal() formula in a column that can count
the cells like;

Formula 1) count the visible cells in a column which start with a text
"C".

Formula 2) count the visible cells in a column which start with a text "C"
&
next number(s) is 2 or more. (e.g. if cell contains text like "C1df",
subtotal will not count, if the cell contains text like "C2dfg" or
"C10jky"
or "C6KL", then subtotal will consider to count this.).

Please try to help me on the above subject.

--
regards,

There is a SUBTOTAL function and it can be used together with SUMPRODUCT to
get a count, sum etc with criteria.


http://tinyurl.com/5hwu7v

--


Regards,


Peo Sjoblom

"Fred Smith" wrote in message
...
This won't be possible with Subtotal. There is no "SubtotalIf" function in
Excel.

The best solution is likely Sumproduct. In this function, you can check
for a starting "C", as well as the filter conditions that create the
visitble cells.

Your second request also has difficulties because you want to consider 10
greater than 2, which is true if it's a number, but not when it's text.
The easiest solution would be to create a helper column with the number in
it. Then it's easy to compare.

Post back if you need more help.

Regards,
Fred.

"driller" wrote in message
...
First of all, i hope this is easy.

Is it possible *?* to have a subtotal() formula in a column that can
count
the cells like;

Formula 1) count the visible cells in a column which start with a text
"C".

Formula 2) count the visible cells in a column which start with a text
"C" &
next number(s) is 2 or more. (e.g. if cell contains text like "C1df",
subtotal will not count, if the cell contains text like "C2dfg" or
"C10jky"
or "C6KL", then subtotal will consider to count this.).

Please try to help me on the above subject.

--
regards,

Impressive.

Picking out the number following the "C" is still going to be an issue,
depending on how many digits need to be supported.

Fred.

"Peo Sjoblom" wrote in message
...
There is a SUBTOTAL function and it can be used together with SUMPRODUCT
to get a count, sum etc with criteria.


http://tinyurl.com/5hwu7v

--


Regards,


Peo Sjoblom

"Fred Smith" wrote in message
...
This won't be possible with Subtotal. There is no "SubtotalIf" function
in Excel.

The best solution is likely Sumproduct. In this function, you can check
for a starting "C", as well as the filter conditions that create the
visitble cells.

Your second request also has difficulties because you want to consider 10
greater than 2, which is true if it's a number, but not when it's text.
The easiest solution would be to create a helper column with the number
in it. Then it's easy to compare.

Post back if you need more help.

Regards,
Fred.

"driller" wrote in message
...
First of all, i hope this is easy.

Is it possible *?* to have a subtotal() formula in a column that can
count
the cells like;

Formula 1) count the visible cells in a column which start with a text
"C".

Formula 2) count the visible cells in a column which start with a text
"C" &
next number(s) is 2 or more. (e.g. if cell contains text like "C1df",
subtotal will not count, if the cell contains text like "C2dfg" or
"C10jky"
or "C6KL", then subtotal will consider to count this.).

Please try to help me on the above subject.

--
regards,

the numbers after C had 2 digit max (e.g C1*- up to C99*).
thanks for trying to help.
--
regards,



"Fred Smith" wrote:

Impressive.

Picking out the number following the "C" is still going to be an issue,
depending on how many digits need to be supported.

Fred.

"Peo Sjoblom" wrote in message
...
There is a SUBTOTAL function and it can be used together with SUMPRODUCT
to get a count, sum etc with criteria.


http://tinyurl.com/5hwu7v

--


Regards,


Peo Sjoblom

"Fred Smith" wrote in message
...
This won't be possible with Subtotal. There is no "SubtotalIf" function
in Excel.

The best solution is likely Sumproduct. In this function, you can check
for a starting "C", as well as the filter conditions that create the
visitble cells.

Your second request also has difficulties because you want to consider 10
greater than 2, which is true if it's a number, but not when it's text.
The easiest solution would be to create a helper column with the number
in it. Then it's easy to compare.

Post back if you need more help.

Regards,
Fred.

"driller" wrote in message
...
First of all, i hope this is easy.

Is it possible *?* to have a subtotal() formula in a column that can
count
the cells like;

Formula 1) count the visible cells in a column which start with a text
"C".

Formula 2) count the visible cells in a column which start with a text
"C" &
next number(s) is 2 or more. (e.g. if cell contains text like "C1df",
subtotal will not count, if the cell contains text like "C2dfg" or
"C10jky"
or "C6KL", then subtotal will consider to count this.).

Please try to help me on the above subject.

--
regards,

Well, at least the first part is easy. You use Peo's solution to check for
filtered records, and add a check for Left(range,1)="C"

For your second problem, I would create a helper column that extracts the
number which follows the C. Then you simple check whether it's greater than
1.

If you can't, or don't want to, create a helper column, I suggest you create
a new post specifically asking how you create a Sumproduct entry checking
for greater than 1 in your sample data. I'm sure someone smarter than I will
be able to help you.

Regards,
Fred.

"driller" wrote in message
...
the numbers after C had 2 digit max (e.g C1*- up to C99*).
thanks for trying to help.
--
regards,



"Fred Smith" wrote:

Impressive.

Picking out the number following the "C" is still going to be an issue,
depending on how many digits need to be supported.

Fred.

"Peo Sjoblom" wrote in message
...
There is a SUBTOTAL function and it can be used together with
SUMPRODUCT
to get a count, sum etc with criteria.


http://tinyurl.com/5hwu7v

--


Regards,


Peo Sjoblom

"Fred Smith" wrote in message
...
This won't be possible with Subtotal. There is no "SubtotalIf"
function
in Excel.

The best solution is likely Sumproduct. In this function, you can
check
for a starting "C", as well as the filter conditions that create the
visitble cells.

Your second request also has difficulties because you want to consider
10
greater than 2, which is true if it's a number, but not when it's
text.
The easiest solution would be to create a helper column with the
number
in it. Then it's easy to compare.

Post back if you need more help.

Regards,
Fred.

"driller" wrote in message
...
First of all, i hope this is easy.

Is it possible *?* to have a subtotal() formula in a column that can
count
the cells like;

Formula 1) count the visible cells in a column which start with a
text
"C".

Formula 2) count the visible cells in a column which start with a
text
"C" &
next number(s) is 2 or more. (e.g. if cell contains text like "C1df",
subtotal will not count, if the cell contains text like "C2dfg" or
"C10jky"
or "C6KL", then subtotal will consider to count this.).

Please try to help me on the above subject.

--
regards,

Fred.

I actually think it is better to use helper columns, it is definitely better
from a design point of view even if it is impressive
that some can come up with these formulas. I believe the originator is
Laurent Longre, the same person who wrote Morefunc which I personally think
is a great add-in.

--


Regards,


Peo Sjoblom

"Fred Smith" wrote in message
...
Well, at least the first part is easy. You use Peo's solution to check for
filtered records, and add a check for Left(range,1)="C"

For your second problem, I would create a helper column that extracts the
number which follows the C. Then you simple check whether it's greater
than 1.

If you can't, or don't want to, create a helper column, I suggest you
create a new post specifically asking how you create a Sumproduct entry
checking for greater than 1 in your sample data. I'm sure someone smarter
than I will be able to help you.

Regards,
Fred.

"driller" wrote in message
...
the numbers after C had 2 digit max (e.g C1*- up to C99*).
thanks for trying to help.
--
regards,



"Fred Smith" wrote:

Impressive.

Picking out the number following the "C" is still going to be an issue,
depending on how many digits need to be supported.

Fred.

"Peo Sjoblom" wrote in message
...
There is a SUBTOTAL function and it can be used together with
SUMPRODUCT
to get a count, sum etc with criteria.


http://tinyurl.com/5hwu7v

--


Regards,


Peo Sjoblom

"Fred Smith" wrote in message
...
This won't be possible with Subtotal. There is no "SubtotalIf"
function
in Excel.

The best solution is likely Sumproduct. In this function, you can
check
for a starting "C", as well as the filter conditions that create the
visitble cells.

Your second request also has difficulties because you want to
consider 10
greater than 2, which is true if it's a number, but not when it's
text.
The easiest solution would be to create a helper column with the
number
in it. Then it's easy to compare.

Post back if you need more help.

Regards,
Fred.

"driller" wrote in message
...
First of all, i hope this is easy.

Is it possible *?* to have a subtotal() formula in a column that can
count
the cells like;

Formula 1) count the visible cells in a column which start with a
text
"C".

Formula 2) count the visible cells in a column which start with a
text
"C" &
next number(s) is 2 or more. (e.g. if cell contains text like
"C1df",
subtotal will not count, if the cell contains text like "C2dfg" or
"C10jky"
or "C6KL", then subtotal will consider to count this.).

Please try to help me on the above subject.

--
regards,