Enhanced Version of Excel's COUNTIF formula

Enhanced Version of Excel's COUNTIF formula

October 9, 2015

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

 

 

 

 

Please reload

Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

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