Countif/Sumif

[Cain]

Hi,

I have been playing with OpenOffice 2.0 Calc and found that it had
additional criteria in the COUNTIF and SUMIF functions, such that it is
possible to do a COUNTIF or SUMIF using a regular expression as a criteria
e.g.

If I wanted to count all cells in column B that started with the word
TRANSFER my COUNTIF statement would look like this:

=COUNTIF(B2:B20,"TRANFER.*)

Can anyone here tell me how it would be possible to achieve the same
results in Excel, please?

 

[Dave Peterson]

Didn't you get this answered yesterday?

=COUNTIF(B2:B20,"TRANsFER*)

(spelling error corrected, too.)

 

[Ron Rosenfeld]

Hi,

I have been playing with OpenOffice 2.0 Calc and found that it had
additional criteria in the COUNTIF and SUMIF functions, such that it is
possible to do a COUNTIF or SUMIF using a regular expression as a criteria
e.g.

If I wanted to count all cells in column B that started with the word
TRANSFER my COUNTIF statement would look like this:

=COUNTIF(B2:B20,"TRANFER.*)

Can anyone here tell me how it would be possible to achieve the same
results in Excel, please?

Try:

=COUNTIF(B2:B20,"Transfer *")+COUNTIF(B2:B20,"Transfer")






--ron

 

[Cain]

Didn't you get this answered yesterday?

=COUNTIF(B2:B20,"TRANsFER*)

(spelling error corrected, too.)

I did indeed, at http://www.excelforum.com/. I thought something had gone
awry with this post because it didn't appear for hours. Anyway, I got told
that

=COUNTIF(B2:B20,"TRANSFER*")

would be correct, and having tried it, it works like a charm.

I have also tried using the single char wildcard of "?", which work very
well.

Do you know where I could find more information on this functionality
because information on criteria seems to be pretty scant?

Btw, thanks for correcting my typos

 

[Ron Rosenfeld]

Didn't you get this answered yesterday?

=COUNTIF(B2:B20,"TRANsFER*)

(spelling error corrected, too.)

I thought the OP wanted to count cells that started with just the word --
Transfer -- and not with words that started the sentence and started with
Transfer: e.g. Transferability -- Hence, my somewhat different answer.

=COUNTIF(B2:B20,"Transfer *")+COUNTIF(B2:B20,"Transfer")


--ron

 

[Dave Peterson]

Excel's help for =countif() and wildcards are the only two locations that I
know.

 

[Cain]

Excel's help for =countif() and wildcards are the only two locations that I
know.

I was rather hoping there was more to it than that. My issue arose from
bringing an exported OOo 2.0 spreadsheet into Excel. After seeing that
certain of the formulas didn't make the journey very well I went into the
OOo regexp functionality in some detail, and began to wonder if Excel had
similar functionality ... ah well.

It turned out that I didn't need it and Excels wildcards were powerful
enough to do the job.

Thanks again for the help.

 

[Dave Peterson]

There are some people who use Open office who may be able to chime in, but I'm
not one of them.

I have seen posts that recommend Laurent Longre's addin:
MoreFunc.xll
from http://xcell05.free.fr/

It has a function that supports regular expressions.

 

[Cain]

There are some people who use Open office who may be able to chime in, but I'm
not one of them.

I have seen posts that recommend Laurent Longre's addin:
MoreFunc.xll
from http://xcell05.free.fr/

It has a function that supports regular expressions.

Thanks for that URL. Laurent Longre's function REGEX.COUNT appears to do
exactly what I'm after, in fact if all functions work as billed his add-in
appears to be a one-stop shop for me.

Brgds,