|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
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:
This added all values in column A where the customer name equals "ABC
Ltd." AND the product purchased equaled that one specific item. This
I added the variable to the customer name so that it would search for
whatever I typed into cell F1:
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:
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
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:
- 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
Any and all help welcome.