Ask Your Question
1

COUNTING TEXT IN COLUMNS [closed]

asked 2013-04-17 03:58:57 +0100

Nifty gravatar image

I have columns with a mixture of text and numbers eg

CM71846 CF21546 CM78946 CF12456 CF24566

How can I count the number of CF and number of CM please

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-30 17:38:28.248604

Comments

@Nifty -- If one of the answers below works well for you, please mark it as correct.

Thanks!

qubit gravatar imagequbit ( 2013-04-27 20:39:32 +0100 )edit

Thanks great help by all

Nifty gravatar imageNifty ( 2013-05-02 11:31:13 +0100 )edit

3 Answers

Sort by » oldest newest most voted
3

answered 2013-04-17 14:14:50 +0100

JohnSUN gravatar image

If you have many text variations, you can use a pivot table

Quick Counting With Pivot Table

edit flag offensive delete link more
2

answered 2013-04-17 10:35:49 +0100

m.a.riosv gravatar image

There is a function to do it:

=COUNTIF(A1:A10;"CF.*")

Please take a look in the help about the function and the regular expressions.

edit flag offensive delete link more

Comments

Sorry I tried that and for sum reason function would not work returns 0 all the time.

Nifty gravatar imageNifty ( 2013-05-02 11:30:33 +0100 )edit

@Nifty regular expressions can be disabled in the (advanced) options/settings screen. Perhaps yours is disabled? CF.* would match anything containing CF, followed by any number of characters.

artfulrobot gravatar imageartfulrobot ( 2014-08-26 16:11:08 +0100 )edit
1

answered 2013-04-17 04:22:38 +0100

razon_22 gravatar image

image description

I'm assuming that you only have one letter/number combination in each cell. Use the image above as a guide. In the example, paste this formula in cell B2:

=IF(LEFT(A2,2)="CF", 1, 0)

and this one in C2:

=IF(LEFT(A2,2)="CM", 1, 0)

Drag the cell handle of each of these cells down to fill the formulas down to the end of your data. Then sum columns B and C.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2013-04-17 03:58:57 +0100

Seen: 21,961 times

Last updated: Apr 17 '13