top of page

Enhanced Version of Excel's COUNTIF formula


One of the more well known Excel formulas is COUNTIF, which will tell you the number of times a value appears in a range of cells. However this formula will only work if the value you are searching for is the only entry in a cell. This alternative formula:

=SUM(LEN(<range>)-LEN(SUBSTITUTE(<range>,"text","")))/LEN("text")

. . . will calculate the number of times a string is listed in a single cell. An example of a situation in which this would be helpful is where text has been collected from a PDF or multiple PDFs where different page ranges are noted in the footer as 'Page 1 of X'; 'Page 2 of X'; and so forth. You'd want to confirm that the number of 'Page' strings in each cell is the same as the last reference in the cell to 'Page X of X'.

You replace, '<range>' with the specific cell you need to search, and then enter the string you are looking for as a substitute for 'text'. See the examples in the screen grabs below.


 
 

Sean O'Shea has more than 20 years of experience in the litigation support field with major law firms in New York and San Francisco.   He is an ACEDS Certified eDiscovery Specialist and a Relativity Certified Administrator.

The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer.

If you have a question or comment about this blog, please make a submission using the form to the right. 

Your details were sent successfully!

© 2015 by Sean O'Shea . Proudly created with Wix.com

bottom of page