Using a 'string inside a string' test inside a formula

Giganews Newsgroups
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

Replies