Complicated SUM IF formula

M

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

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

Don Guillett

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top