please try this

[driller]

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.

 

[Fred Smith]

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.

 

[Peo Sjoblom]

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

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.

 

[Fred Smith]

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.

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

 

[driller]

the numbers after C had 2 digit max (e.g C1*- up to C99*).
thanks for trying to help.
--
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.

 

[Fred Smith]

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.

 

[Peo Sjoblom]

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