Sum the corresponding cell value if the criterion is matched

[ptang978]

Hi All,

I'm trying to add up the values in a different column when certain criterion are met.

Assuming I have 5 different strings of numbers:

Column A Column B
12-123-1234-12345 100
99-123-1234-12345 40
12-555-9999-12345 70
12-645-9999-12345 130
99-111-9674-12345 15

I would like to SUM rows 1, 3 and 4 together because they both begin with '12' and end with '12345'.

The results should look like such:

Column C Column D (SUM)
12-XXX-XXXX-12345 300
99-XXX-XXXX-12345 65

If you'd like to see another example like this one, open the following: Excel Help.xls

Any help would be appreciated.

Thanks,
Phil

[Melvinrobb]

This formula should work in the attached file:
=SUMIF($B$1:$B$20,LEFT(B1,4)&"???-????"&RIGHT(B1,6),$C$1:$C$20)

[FDibbins]

Hi and welcome to the forum

1st, please note that you have an extra character at the start of all the entries in A, so I had to make allowances for that. If you can eliminate that, the formula will be a bit simpler.

Try this approach. create a helper column in D and copy this down...
=LEFT(B1,3)&RIGHT(B1,5)
This will extract the numbers you want.

Then use this for the total...
=SUMIF(D1:D20,CHAR(160)&"1900092",C1:C20)

the CHAR(160) is to cater for the extra character at the start of your data, if you can remove the extra character, then you can remove that part of the formula
Also, I have hard-coded the 19 and 00092, you could reference them in cells if you wanted more flexibility

[ptang978]

Thanks, you guys are awesome! FDibbins, thanks for the in-depth explanation!

[FDibbins]

Happy to help and thanks for the feedback

[jiver1]

If anyone can help, I have created a very basic sumif formula and it is just returning the corresponding row's value, in this case 15. I am actually trying to use this between two sheets and it is doing the same thing where it is not searching based on the criteria, but rather only giving the value from the corresponding row. Any help is appreciated. The formula in Cell F1 is =sumif(A1:A3,A1,D1:D5). Capture.PNG