Sunday, July 15, 2012

yogi_Count Instances Of Specified Words Based On Multiple Conditions Within Strings Of Text in A Column

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #631   Jul 15, 2012     www.energyefficientbuild.com.


user Swanson said:
How do I use a countifs equivalent utilizing a partial cell content? 
Hi all,
I have been able to successfully use countifs substitutes by utilizing many solutions already posted on this forum but have run into a unique challenge I am hoping somebody can help with. Here is my issue:
I have been using regular countif formulas to count the number of cells that contain certain words within them by utilizing a look for like "*other*" or whatever the word may be. Now, what I need to do is use a countifs substitute that will allow me to look for parts of a cell. 
This is an example of a countifs substitute I have used in another situation but it does not seem to work if instead of "yes", I utilize "*yes*" because the cells would also contain other words in it.
C13>0,(Sumproduct(('Student Data'!BU:BU="Fall")*('Student Data'!L:L="yes"))+index(0;1)
Is there any type of formula that will allow me to account for partial cell contents?
Thanks,
Swanson

-----
Below is the link to the file.
https://docs.google.com/spreadsheet/ccc?key=0AmQZb7kPQv7ZdFhtblFId2tEa1EwdzNGLXlPMGhlVFE#gid=0

Let me know if that doesn't work. Thanks!
---------------------------------------------------------------------------------------
following is a solution to the problem