Software

Ask something
Question Locked

Sumif with Find and Mid

0 helpful
3 replies

by juaniyoegy

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Your June 8,2000 article "Using Excel's Find and Mid to extract a substring when you don't know the start point" is perfect for my application...I now need to combine it with a sumif statement, if possible. Let's say that I would need to sum sales for all products with the code B3? The article found 2 instances of the code B3 after the dash, for example. (Yes, I know I can easily insert column for the resulting code, but the spreadsheet is getting unruly and I want to minimize adding extra columns). Thanks!

3 replies

by robshan

If I have understood your question the following should help you.

If you use the sheet in the original article and put the sales figures in Column C, the following formula will work:

=SUMIF(A2:A8,"*B3*",C2:C8)

A2:A8 being the range with part numbers and C2:C8 being the corresponding sales.

If you want the formula to be a little more useful and be able to specify a code on the fly this will work:

=SUMIF(A2:A8,"*" & D1 & "*",C2:C8)

D1 being the cell where you enter the code you are interested in.

by juaniyoegy

Thanks, RobShan, but the formula I'm looking for is without having to make the "B" column in the example. My spreadsheet, in its simplest form, would have the "Source" column as column "A" and the sales figures in column "B". I agree that your suggestion is the simplest if it was simply one column that I'll be adding, unfortunately, that's not the case. I am able to use the sum arrays for multiple criteria, but that doesn't help me if in this case. Much appreciated.

This discussion is closed to new replies.