Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complicated Formula | Excel Worksheet Functions | |||
Complicated formula? | Excel Discussion (Misc queries) | |||
Need help doing complicated formula | Excel Worksheet Functions | |||
Complicated formula | Excel Worksheet Functions | |||
Complicated Formula | Excel Discussion (Misc queries) |