In excel, want to only sum certain values(not as easy as SUMIF)?

So I have two columns on named program and one with cost values. The three programs are ABC, A, B, and C. I want to sum the costs of all programs that contain A. All that contain B. And all that contain C. ABC clearly is in开发者_StackOverflow中文版cluded in all the sums. The problem is that to get just these programs the spreadsheet has a filter on it which messes sumif up. Can someone help? Here is an example of what I mean:

program     cost
A           5.00
B           4.00
ABC         9.00
A           2.00

so I would want in three separate cells "sum with A"=16.00, "sum with B"=13.00, "sum with C"=9.00.

---

Item | Total
A    | 16
B    | 13
C    | 9

Assuming your above range is in A1:B5, my first formula is the following Array formula:

{=SUM(IF(ISERROR(FIND(B6,$A$1:$A$5)),0,$B$1:$B$5))}

You create an Array formula by entering the formula and holding down the Ctrl+Shift keys while you hit Enter. In my solution, I've created an area where I calculate by totals and have a column (called Item in this case) which indicates the letter I see in the original A column.

If you were trying to enter this using VBA, you would use the FormulaArray property:

Selection.FormulaArray ="SUM(IF(ISERROR(FIND(B6,$A$1:$A$5)),0,$B$1:$B$5))"

Update

Restricting the calculation to only visible cells is a bit more complicated. Suppose we have your original data in cells A1:B5. Let's also suppose our test values start in cell C7 (diagonal to the source data). Our totals formula would look like:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($B$1:$B$5,ROW($B$1:$B$5)-ROW($B$1),0,1)), --NOT(ISERROR(FIND(C7,$A$1:$A$5))), $B$1:$B$5)

The following portion returns a range over the cells

OFFSET($B$1:$B$5,ROW($B$1:$B$5)-ROW($B$1),0,1)

This portion returns 1 for each visible cell and 0 for invisible cell

SUBTOTAL(3,OFFSET($B$1:$B$5,ROW($B$1:$B$5)-ROW($B$1),0,1))

This portion is our criteria. NOT(ISERROR(... will return TRUE or FALSE. The double negative sign -- converts that value into a negative integer and then removes that negation.

--NOT(ISERROR(FIND(C7,$A$1:$A$5)))

Lastly, the SUMPRODUCT function multiplies the matching arrays to each other and executes the sum. The first two arrays return a series of 0's or 1's. If the row is both visible and matches our criteria, then we get 1*1 multipled by the given value in the cell. If the given cell is not visible or does not match the criteria, one of the two return a zero and it zeroes out the entire item.