Complicated SUM IF formula
 

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.

Complicated SUM IF formula
 

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))

--
Biff
Microsoft Excel MVP


"mckzach" wrote in message
...
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.

Complicated SUM IF formula
 

In this case
=SUMPRODUCT((LEFT(I2:I22,1)="1")*H2:H22)
or
=SUMPRODUCT(--(LEFT(I2:I22,1)="1"),H2:H22)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"mckzach" wrote in message
...
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.