- You are here
- Microsoft Newsgroups Archived.At
- microsoft.public.excel
- 2007 January
- Using a 'string inside a string' test inside a formula

Subject: | Using a 'string inside a string' test inside a formula |

Posted by: | Nikko963 (lynn_ni…@hotmail.com) |

Date: | 21 Jan 2007 |

Hi, all. Thank you in advance for your help with this problem that has

bugged me know for weeks.

My problem is this: my company sells only four basic categories of

items but, within each category, there can be as many as 15 variations.

The product description codes in our database are cryptic and only a

single digit in that long code identifies the basic category of each

item.

So, I have a spreadsheet with a list purchases that looks like this:

Units Product Customer

5 XYZ_1_verA ABC Ltd.

3 XYZ_1_verB XYZ Inc.

19 ABC_2_verA LMN Corp.

4 XYZ_1_verC ABC Ltd.

The number in the product description indicates into which product

category the specific product purchased falls. In the above example, a

total 12 units of category one were purchased and 19 of category 2.

Customer ABC purchased 9 units of product category one and XYZ

purchased three. LMN purchased 19 of product category two.

Elsewhere on the sheet, I want to be able to enter the customer name

into a cell and, in the cells below, I want to show the number total

number of units purchased of each of the four product categories. At

first, I thought it would be simple, using an example of a multiple

condition sum array straight from the Help file. To test it I wrote:

=SUM(IF((c:c="ABC Ltd.")*(B:B="XYZ_1_verA"),A:A))

This added all values in column A where the customer name equals "ABC

Ltd." AND the product purchased equaled that one specific item. This

worked well.

I added the variable to the customer name so that it would search for

whatever I typed into cell F1:

=SUM(IF((c:c=F1)*(B:B="XYZ_1_verA"),A:A))

This worked too.

Now, the challenge: I need to sum the units purchased of each category

of product, not the specific product. I thought this would be easy by

using a wildcard to focus on the category number:

=SUM(IF((c:c=F1)*(B:B="*1*"),A:A))

But it interprets "*1*" literally, not as "anything before a '1'and

anything after". In fact, after testing, I realized that even a basic

"if" function will not accept a wildcard in a string test (ie: the

formula =if(B1="*1*", "true", "false") will only show "true" if the

cell B1 contains, literally, "*1*").

QUESTION 1: Is there a way to force a formula/function to use the

wildcard?

I looked at the Search/Find function but, while it will report a value

(and thus "true") when it finds the number in the cell, it generates

and error if it doesn't find it and is thus useless. I thought of using

"instr" but that VB function doesn't appear to be supported in an Excel

cell formula. I also thought that using Countif might work - as in:

=sum(if((c:c=F1)*(countif(B:B,"=*1*")),A:A))

- since Countif and Sumif seem to work with wildcards. Alas, this just

totals up ALL purchases by the customer in F1.

QUESTION 2: is there a way of doing this at all?

I am flabbergasted that Excel doesn't seem to include a function for

doing this. Of course, maybe it's just me (or make that it's probably

just me).

Any and all help welcome.

Nikko

- Re: Using a 'string inside a string' test inside a formula posted by RichardSchollar on 21 Jan 2007
- Re: Using a 'string inside a string' test inside a formula posted by Nikko963 on 21 Jan 2007
- Re: Using a 'string inside a string' test inside a formula posted by Ron Rosenfeld on Sun, 21 Jan 2007
- Re: Using a 'string inside a string' test inside a formula posted by T. Valko on Sun, 21 Jan 2007