Complicated SUM IF formula

[mckzach]

I have side-by-side columns; Col. A containing dollar amounts, Col. B
containing numeric exception reasons as below:

Col A Col B
$125 1
$250 2
$75 1,3
$500 4
$200 1,2

The following formula works perfectly fine if I'm only interested in the
number of occurrences of Reason 1 in Col. B (3 occurrences above).

=SUM(LEN($B$10:$B$4000)-LEN(SUBSTITUTE($B$10:$B$4000,1,"")))

Now I need the total dollar amount in Col. A if Col. B equals 1 OR contains
1. In the example above, that answer would be $400 (or $125 + $75 + $200).

How would you alter the formula to return the dollar amount(s)?
Any help at all would be appreciated. Thanx in advance.

 

[T. Valko]

It depends on how many exception codes there are. For example, if there are
codes 1, 10,11, 21 this will be difficult because you have to be able to
distinguish the 1 as a unique code that is separate from 10,11,21.

You would make your life much easier if you separated the codes to
individual cells. This can be done easily using the Text to Columns feature.

If 1 is the only code that contains the digit 1:

=SUM(SUMIF(B1:B5,{"*1*",1},A1:A5))

 

[Don Guillett]

In this case
=SUMPRODUCT((LEFT(I2:I22,1)="1")*H2:H22)
or
=SUMPRODUCT(--(LEFT(I2:I22,1)="1"),H2:H22)