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.

The source of this formula is: https://support.microsoft.com/en-us/kb/187667


Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

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