Counting Multiple Occurances of A Text within a String Over a Range...

What if you have a list of string and you want to compile a list of them contain certain word and you want to count a certain text that appears multiple times with a string. Lets examine the following list:

My name is faseeh faseeh
My name is not faseeh
My Name is faraz
My Name is fawwad
My Name is farooq


Each of the them contains "My Name", two of them contains "faseeh", all five contains "is". Now the question is that how will we find the string contains "Faseeh"? 


The COUNTIF() with wildcard can calculate the frequency of the text but it will not count for the text that appears twice within these string.

The following formula does the trick, Enter and Press Control+Shift+Enter, and drag down.

=IFERROR(INDEX($A$1:$A$5,SMALL(IF(IFERROR(SEARCH($B$1,$A$1:$A$5,1)>=1,0),ROW($A$1:$A$5)),ROW(C1)),0),"---")

The Search() Function:

IF(IFERROR(SEARCH($B$1,$A$1:$A$5,1)>=1,0)

The Search function looks up for the Lookup_Criteria, which is present in B1 (i.e Faseeh) in the Array A1:A5, if the search is gives VALUE# Error, the IFERROR() formula replaces the error with zero. This in retrun is feed to the IF() that return ROW() # for the Trues. 

=IFERROR(INDEX($A$1:$A$5,SMALL({1,2,FALSE,FALSE,FALSE,ROW(C1)),0),"---")

The Small() Function: 

The SMALL() function returns the first smallest value in the array, the result is feed to the second argument of INDEX() function that is a row number, with column offset equals to zero, if the result is an error the outside IFERROR() formula wraps it into and gives you "---". 

Hope this post helps you once again.

The SUMPRODUCT() function...

Introduction:

The SUMPRODUCT() function is a very important function when it comes to validiate multiple conditions and sum a certain range that satifies the given criteria. In this post i will explain and highlight some of the very commonly encountered situtation where SUMPORDUCT() function can be used.

The Syntex:

The MS Excel 2007 describes it as:

SUMPRODUCT(array1,array2,array3, ...)

Array1, array2, array3, ...   are 2 to 255 arrays whose components you want to multiply and then add.

With Remarks that:
1. The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value. 
2. SUMPRODUCT treats array entries that are not numeric as if they were zeros

Senario # 01: 

The basic usage of SUMPRODUCT() is when we try to multiple different ranges of equal diemntions and want to get their sum. The attached sample file has tab Senario # 01 that describes the process so it will easy if you download it:

Product Price Quaintity

A 10 50
B 15 55
C 20 40
D 25 35

For the given example that consitute the Range from A1:C5, we enter following formula in Cell C6 to get the PRODUCT+SUM of Price & Quaintity:

=SUMPRODUCT($B$2:$B$5,$C$2:$C$5)   

If you select the cell C6 and go to Tab Formula > Evaluate Formula it will take you from follwing steps:

Step 01: =SUMPRODUCT($B$2:$B$5,$C$2:$C$5)   
Step 02: =SUMPRODUCT((10,15,20,25,),(50,55,40,35))
Step 03: =SUMPRODUCT((500,825,800,875))
Step 04: =3000

Thus an additional column that had been need to multiply the Price and Quaintity has been avoided and we get the same result.

Senario # 02:

The SUMPRODUCT() function can be used to verifiy multiple conditions. Contining with the same example of Product, Price & Quaintity, we add some further details to elaborate this type of usage as well. Please switch to the second sheet named "Senario # 02" to understand this example:

Product Month Price Quaintity
A Jan, 12 10 50
B Feb, 12 15 55
C Mar, 12 20 40
A Jan, 12 25 35
B Feb, 12 35 15
B Mar, 12 40 20
C Apr, 12 45 27

First Situation: Summing the Quaintity for a particular month:

In such a case we will setup a cell to input the "Month" for which we want to sum the sales. The formula in this case will look like:

=SUMPRODUCT(($B$2:$B$8=$C$11)*(D2:D8))

For this formula to work we will enter in $C$11 the desired month eg "Jan,12", the formula will work like this:

Step 01: =SUMPRODUCT(($B$2:$B$8=$C$11)*(D2:D8))
Step 02: =SUMPRODUCT(({"Jan, 12","Feb, 12","Mar, 12","Jan, 12","Feb, 12","Mar, 12","Apr, 12",=$C$11)*(D2:D8))
Step 03: =SUMPRODUCT(({"Jan, 12","Feb, 12","Mar, 12","Jan, 12","Feb, 12","Mar, 12","Apr, 12",="Jan, 12"}))*(D2:D8))
Step 04: =SUMPRODUCT({True,False,False,True,False,False,False}*{50,55,40,35,15,20,27})
Step 05: =SUMPRODUCT({50,False,False,35,False,False,False})
Step 06: =85

Second Sitatuion: If we want to get "Total Cost" then we will multiply it with ($C$2:$C$8), the formula will look like then

=SUMPRODUCT(($B$2:$B$8=$C$11)*($C$2:$C$8)*($D$2:$D$8))

Step 01: =SUMPRODUCT(($B$2:$B$8=$C$11)*(D2:D8))
Step 02: =SUMPRODUCT(({"Jan, 12","Feb, 12","Mar, 12","Jan, 12",...,"Apr, 12",=$C$11)*($C$2:$C$8)*(D2:D8))
Step 03: =SUMPRODUCT(({"Jan, 12","Feb, 12","Mar, 12","Jan, 12",...,"Apr, 12",="Jan, 12"}))*($C$2:$C$8)*(D2:D8))
Step 04: =SUMPRODUCT({True,False,False,True,...,False}*{10,15,20,25,35,40,45}*{50,55,40,35,15,20,27})
Step 05: =SUMPRODUCT({500,False,False,875,False,False,False})
Step 02: =1375   

Third Situation: Consider the thrid sheet where we have to check for Month as well as week, the Month is present in columns while week numbers are present as the header row of the table. We have incorporated an option in the workshee that will look into the range of the weeks specified through input cell C7:C8, the month is specified in C9. The table looks like this:

Product Month Price 1 2 3 4 5
A Jan, 12 10 40 10 30 30 50
B Feb, 12 15 50 20 10 30 20
C Mar, 12 20 40 10 30 10 20

And the formula is: 

=SUMPRODUCT(($B$3:$B$5=$C$9)*($C$3:$C$5)*($D$2:$H$2>=$C$7)*($D$2:$H$2<=$C$8)*($D$3:$H$5))

Step 01: =SUMPRODUCT({"Jan, 12","Feb, 12","Mar, 12"}="Jan, 12"*($C$3:$C$5)*($D$2:$H$2>=$C$7)*($D$2:$H$2<=$C$8)*($D$3:$H$5))
Step 02: =SUMPRODUCT({True,False,False}*{10,15,20}*{1,2,3,4,5>=1}*{1,2,3,4,5<=2}*($D$3:$H$5))
Step 03: =SUMPRODUCT({True,False,False}*{10,15,20}*{1,2,3,4,5>=1}*{1,2,3,4,5<=2}*{40,10,30,30,50,.....20,40,10,30,10,20})
Step 04: =SUMPRODUCT({10,0,0}*{1,2,3,4,5>=1}*{1,2,3,4,5<=2}*{40,10,30,30,50,.....20,40,10,30,10,20})
Step 05: =SUMPRODUCT({True,True,True,True,True}*{1,2,3,4,5<=2}*{40,10,30,30,50,.....20,40,10,30,10,20})
Step 06: =SUMPRODUCT({10,10,10,10,10,0,0,0,0,0,0,0,0,0,0}*{True,True,Flase,Flase,Flase}*{40,10,30,30,50,.....20,40,10,30,10,20})
Step 07: =SUMPRODUCT({10,10,0,0,0,0,0,0,0,0,0,0,0,0,0}*{40,10,30,30,50,.....20,40,10,30,10,20})
Step 08: =SUMPRODUCT({400,100,0,0,0,0,0,0,0,0,0,..,0,0,0,0})
Step 09: =500

Fourth Situtation:  We can use "<>" operator to select everything else then a particular criteria, see the fourth sheet for this example: 

The table looks like following:

Product Month Price Quaintity
A Jan, 12 10 50
B Feb, 12 15 55
C Mar, 12 20 40
A Jan, 12 25 35

Lets assume that we can to sum the Total Cost for every thing else then "A" we will replace the equal sign "=" with "<>":

=SUMPRODUCT(($A$2:$A$5<>"A")*($C$2:$C$5)*($D$2:$D$5))

The formula will check the first condition and will give these result: 

Step 01: =SUMPRODUCT({"A","B","C","A"<>"A"}*($C$2:$C$5)*($D$2:$D$5))
Step 02: =SUMPRODUCT({False,True,True,False}*{10,15,20,25}*{50,55,40,35})
Step 03: =SUMPRODUCT({0,825,800,0})
Step 04: =1625

Conclusion: The SUMPRODUCT() function gives you flexibiility to check for multiple criterias and sum a range, in caes of unique value, it can also retrive one that meets your critieria, but being an array formula it works slower then SUMIF().

Creating a Search Styled List in Excel...

What if you have a list of string and you want to compile a list of them contain certain word. The feature is very common when we search for certain file in windows explorer through Search Option.

Lets examine the following list:

My name is faseeh
My name is not faseeh
My Name is faraz
My Name is fawwad
My Name is farooq

Each of the them contains "My Name", two of them contains "faseeh", all five contains "is". Now the question is that how will we find the string contains "Faseeh"? 

The following formula does the trick, Enter and Press Control+Shift+Enter, and drag down.

=IFERROR(INDEX($A$1:$A$5,SMALL(IF(IFERROR(SEARCH($B$1,$A$1:$A$5,1)>=1,0),ROW($A$1:$A$5)),ROW(C1)),0),"---")

The Search Function:

IF(IFERROR(SEARCH($B$1,$A$1:$A$5,1)>=1,0)

The Search function looks up for the Lookup_Criteria, which is present in B1 (i.e Faseeh) in the Array A1:A5, if the search is gives VALUE# Error, the IFERROR() formula replaces the error with zero. This in retrun is feed to the IF() that return ROW() # for the Trues. 

=IFERROR(INDEX($A$1:$A$5,SMALL({1,2,FALSE,FALSE,FALSE,ROW(C1)),0),"---")

The SMALL() function returns the first smallest value in the array, the result is feed to the second argument of INDEX() function that is a row number, with column offset equals to zero, if the result is an error the outside IFERROR() formula wraps it into and gives you "---". 

Hope this post helps you once again.

Looking up & Matching Repeated Values...

Look at the following table, how will you find the Lowest two values and the corresponding Person & Region? The Question had been less trickier if the 5% value had not been repeated twice. In this post i will show you how to retrieve the values that have duplicate appearance in the tables.

Person Region Score
Engineer 2         South 5%
Engineer 3         North 5%
Engineer 8         South 15%
Engineer 1         North 20%
Engineer 4         South 25%
Engineer 5         North 30%
Engineer 10 South 35%
Engineer 7         North 40%
Engineer 6         South 50%
Engineer 9         North 65%

Using the following formula does the task very well. Lets take it up and split to understand how does it works!

=INDEX($A$3:$A$12,MATCH(SMALL(IF($B$3:$B$12=$F$2,($D$3:$D$12)+ROW($D$3:$D$12)*0.0000001),ROW(A1)),($D$3:$D$12)+ROW($D$3:$D$12)*0.0000001,0),0)

=VLOOKUP(F4,$A$3:$D$12,3,FALSE)

Today we will discuss a technique that is useful in getting smallest of values while testing for certain conditions. The usually used formula for getting nth smalles value is SMALL() that works fine for simple tables where we are just interested in the number itself. But when it comes to find the nth smallest number and then finding something against that number then it becomes a challange because there could be multiple enteries of a same value. 

Lets me explain it with this table: 

If i try to found the first three smallest values for the above table we can safely conclude them to be 5%, 5% & 15%. But when we try to fetch the corresponding "Person" against these values using MATCH() function, the result is "Engineer2", "Engineer2" and "Engineer8". So why this. This is because MATCH() always looks for the first match that in encounter swhile looking for a values. so it does not distinguishes between 5% of Engineer2 & 5% of Engineer3!!

The solution to this problem is to make each of these 5% different from each other so that when MATCH() goes for a lookup, it differentiate between the First & the Second 5% respectively. In order to make them different we use following formula. 

The IF() in the above formula IF($B$3:$B$12=$F$2, ($D$3:$D$12)+ROW($D$3:$D$12)*0.0000001) checks for every value in B3:B12 for whether it is "South" or not, if it is "South", the second argument of the function will pass a value that will have added value of ROW()*0.0000001. This results in addition of 0.0000002, 0.0000003, 0.0000004,...., 0.0000010, 0.0000011, 0.0000012 to the each of the %age values thus two 5s are now differentiate as they are 0.0500002 & 0.0500003 respectively, the logic is applied to the entire range. Note that not all the values in the resulting array will be numbers,  only those that are upto the creiteria will be values, rest will be FALSE.

The SMALL() function examines the resulting values [SMALL(0.0500002,0.0500003,0.1500004,0.2000005,...,0.6500011,0.0000012)]
and picks up the first smallest value. 

Now this value needs to be matched with an array of similarly generated values so that we can find its location. the same formula is feed to MATCH()'s second argument so that it can lookup for the values. Once these values are found, INDEX() finds out the corresponding value from Column A to reach the solution. 

Now this values can be VLOOKUP()-ed in the table to get the corresponding values-a thing that has other wise been impossible. 

I hope that you will enjoy the post...am waiting for feedback