# Thread: Inbed search function inside array

1. ## Inbed search function inside array

Loungers, I am using the array formula below to sum the number of occuraces of a value @ \$E8 (ie Level One - Environment - Small localised spill) in a particular month in the worksheet, this work great.

=SUM((Worksheet!\$H\$14:\$H\$999=\$E8)*(DATE(YEAR(Works heet!\$B\$14:\$B\$999),MONTH(Worksheet!\$B\$14:\$B\$999),1 )=K\$7))

The probem is that the data in colum H of the worksheet may contain other text and in different places in the same cell , ie

Level Two - Minor Plant Damage, Report Only, Level One - Environment - Small localised spill, Level One - Financial loss < \$1000
Level One - Environment - Small localised spill, Level Two - Serious Potential Incident, Level Three - Major Plant Damage

Is there away to encorporate a function that will look in the cells and sum the number of occurances (Level One - Environment - Small localised spill) regardless of where the sting is and what other text that there maybe in the cell.

I hope that makes sence.

Any thoughts/suggestions would be much appreciated

Cheers

2. Anyone have any thoughts?

3. ## Good news = a solution; Bad news = you won't like it

I have faced this sort of problem and never found an easy answer. It should be possible using some sort of wild card to stand for the characters you don't need included. BUT...I can't find the wild card function AND you'll find that minor variations mess you up every time. (i.e.: Level One; Level one; Level 1; and level one are not the same.) No one is so well-disciplined that this will not happen.

Here is the best solution I have found:
• Set up your columns to contain just one type of data: Level, Source, Condition, whatever.
• Parse the single data column into these specific columns. Your example makes the '-' and ',' look like the break points for each data type.
• I personally would reduce the data. e.g.: 'Level One' = 1 in the column for Level. So does 'level 1' = 1. Trust me: when you have parsed and sorted the data you will see the variations and they will be numerous.
• You can establish one column that recombines the text formatted for publication in a strict syntax if you wish.
• You can count, sort, and extract the records you want based on the columns.
• As a corollary benefit, your data collection procedure will need to change.
This is a huge hassle but you are better off in the end and your data is more reliable and much more usable. Your spreadsheet is fundamentally flawed because its single-cell/multiple data approach. It is designed for data entry convenience but was not designed to both record and process the data it contains.

~~~~~~~~~~~~~~~~

There is a SEARCH function you can explore. It sets up a sub-table based on the string. Unless your data entry is VERY disciplined this will exclude instances containing alternate forms.

Another solution, depending on your data set size, is to take a pencil and piece of paper and simply count them. Crude, but it does allow visual editing of data variations like 'One', 'one', and '1' (to say nothing of 'I'.)

4. Magna

Thanks for the reply. The data is exported from an online reporting database, so the terminologies and the text always stay the same but can be in a different order each time.
And there can be hundreds of records. So I need to manually go though each record and delete other strings of text, thats why I was hoping that there was away to search for the string using a formula and sum the product. However the different strings are always seperated by a ','

I hope that there is a solution out there someplace.

5. There are several solutions.

Code:
`=--(ISNUMBER(SEARCH("your text"A1)))`
This will return TRUE if your text is included anywhere in cell A1, and FALSE if it is not.

I am guessing from the formula you submitted that you are able to "take it from there". Post back if you can't.

6. ## The Following User Says Thank You to MartinM For This Useful Post:

Magna (2012-10-04)

7. Thanks Martin - Will give your choise a shot tomorrow and see how it goes. Any problems will post back.

8. ## SEARCH should work

MartinM's suggestion should work if the strings are machine-generated. I've never used SEARCH functions but could use them to simplify some operations. Thanks, Martin.

9. Magna - I have embedded your suggestion (where I assume it belongs) into the array formula, but its not returning any results

=SUM((Worksheet!\$H\$14:\$H\$999=--(ISNUMBER(SEARCH("*enviroment*",\$E8)))*(DATE(YEAR( Worksheet!\$B\$14:\$B\$999),MONTH(Worksheet!\$B\$14:\$B\$9 99),1)=K\$7)))

Any thoughts?

10. I don't think that you can use wildcard characters in the way you have. Just pick some unique part of the text in the condition you are testing for.

11. MartinM I tried it without the wildcard but got no results, so tried the wildcards to see if that would work - but no luck either way.

Any other thoughts?

12. Please excuse the hurried response - I am just going out to dinner !

You've got in a muddle with your logic.

Leaving aside your other tests for the moment, the number of occurrences of the string environment in the range \$H\$14:\$H\$999 is given by the array formula

{SUM(--(ISNUMBER(SEARCH("*environment*",\$H\$14:\$H\$999))}

However three thoughts in passing:

1. When you are using the value in E8, either you need to express it as \$E\$8 on your formulas or, better, give it a meaningful NAME.

2. You have mis-spelled environment, maybe just a transcription error in your post ?

2. A SUMPRODUCT expression would IMH0 be much simpler to use in this case.

13. MartinM - thanks for your help. Will try it out and see how it goes on Monday.

Could you expand on the SUMPRODUCT option - also what does IMHO mean?

14. Had a lovely dinner with friends - am now back !

Sorry about the IMHO - I don't usually use these abbreviations but, as I said, I was in a rush: In My Humble Opinion.

There are many full descriptions of SUMPRODUCT on the net but a common way it is used is as follows.

Let's say you want (as you do) to add together items which meet certain criteria. So you would construct an expression as follows:

SUMPRODUCT(<a range of cells containing the data you want to add up>*(criterion1)*(criterion2)*(criterion3) etc)

So it might look like SUMPRODUCT((H1:H200)*(H1:H200>100)*(K1:K200<\$A\$8)* (L1:L200<Now()-2))

The first expression (H1:H200) is the values themselves and the subsequent expressions all return 1 if TRUE and 0 if FALSE:
(H1:H200>100) true if the value itself is greater than 100
(K1:K200<\$A\$8) true if a corresponding value (same row as) in another column is less than some constant or other
(L1:L200<Now()-2) true if a date associated with (same row as) the value is more than 2 days ago

So each item in H1:H200 is only added into the sum if all the criteria are TRUE. If any criterion is FALSE then its value will be 0, the product will be zero, and it will not be added into the sum.

There are a number of provisos, two in particular:

1. Every range must occupy the same rows or columns (hence in the example, all the ranges are nn1:nn200).

2. You can only have 30 criteria - enough for most cases.

Note that in constructing and debugging this sort of long expression the task is always much easier if you give the ranges meaningful names eg

SUMPRODUCT((Invoices)*(Invoices>100)*(ItemsOnInvoi ce<SingleShipmentLimit)*(DeliveryDate<Now()-2))

Hope this helps.

15. Hi MartinM - Glad you enjoyed your meal, I'm sure that it would have been very nice , It will take me awhile to digest your suggestions, will try out the option next week and see how it goes. Thank you for your efforts, will let you know how it goes.

Thanks again

Regards

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•