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

[Nikko963]

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

 

[RichardSchollar]

Hi Nikko

You could certainly use Find/Search:

=SUMPRODUCT(--($C$1:$C$10=F10),--ISNUMBER(FIND("2",$B$1:$B$10)),$A$1:$A$10)

You'll need to adjust your ranges to suit - you can't use whole column
refs with Sumproduct, so the max (in xl2003) will be A1:A65535 for
example.

Hope this helps!

Richard

 

[Ron Rosenfeld]

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.

That's true, but you can use a construct such as:

=ISNUMBER(FIND("1",A1))

which will return TRUE or FALSE depending on the presence or absence of a 1 in
cell A1.


--ron

 

[RichardSchollar]

And just realised I put a 2 in the Find function, not the 1 you asked
for - d'oh!!

 

[T. Valko]

Try this:

F1 = ABC Ltd.
F2 = 1 (product category)

=SUMPRODUCT(--(C1:C4=F1),--(ISNUMBER(FIND(F2,B1:B4))),A1:A4)

Biff

 

[Nikko963]

You folks rock! I've been struggling with this for weeks. Never would
have thought about using the ISNUMBER.

To Microsoft: IT SHOULDN'T HAVE TO BE THIS DIFFICULT!! (For all the
good that's going to do.)

Thanks a million!

Nikko