Countif/Sumif

C

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?
 
D

Dave Peterson

Didn't you get this answered yesterday?

=COUNTIF(B2:B20,"TRANsFER*)

(spelling error corrected, too.)
 
R

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
 
C

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 ;)
 
R

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
 
D

Dave Peterson

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

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.
 
D

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.
 
C

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,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top