SumIf Formula Addition Based On AlphaNumeric Criteria

[Howardc]

I have account numbers in Col A and values in Col E

I would like to add the data as follows

1) Range Col A 3001 to 3007 based on values in Col E
2) Range Col A 3001D to 3007D dased on values in Col E
3) Range Col A 3001K to 3007K dased on values in Col E
4) Range Col A 3500 to 3525 dased on values in Col E
5) Range Col A 4000 to 4007 dased on values in Col E
6) Range Col A 4000A to 4007A dased on values in Col E etc

I have attached sample data

Your assistance in this regard is most appreciated

[LalitPandey87]

I am little bit confused what you have mentioned in requirement. Please make it more understandable. Example would be the best way. :confused:

[Howardc]

thanks for the reply. Have attached example showiing the range of account numbers in Col G and then using the sum function to show the desired result in Col H

What I am trying to do is to have range of values added in Col E based on a range of account numbers in Col A

Hope this example makes it clearer of what I want to achieve

[LalitPandey87]

Copy below formula and paste this in cell H1 with CSE
=SUM(IFERROR((INT($A$1:$A$32) >= 3001)*(INT($A$1:$A$32) <= 3007)*($E$1:$E$32),0))

Copy below formula and paste this in cell H3 with CSE
=SUM(IFERROR((RIGHT($A$1:$A$32,1)="D")*(--(SUBSTITUTE($A$1:$A$32,"D",""))>=3001)*N(--(SUBSTITUTE($A$1:$A$32,"D",""))<=3007),0)*$E$1:$E$32)

Copy below formula and paste this in cell H5 with CSE
=SUM(IFERROR((INT($A$1:$A$32) >= 3500)*(INT($A$1:$A$32) <= 3515)*($E$1:$E$32),0))

As all these are array formula so please apply it with Control + Shift + Enter :cheers:

[Howardc]

Thanks for the help. This much appreciated

[Howardc]

Dear Lalit

Thanks for your help. These are really interesting formulas.

Your second formula did not work and Excel Fox assisted me here http://www.excelfox.com/forum/f2/sumproduct-formula-sum-range-criteria-numeric-text-891/

He changed it to =SUM(IFERROR((INT(SUBSTITUTE(Sheet1!$C$5:$C$1000,"D","0")) >= 30000)*(INT(SUBSTITUTE(Sheet1!$C$5:$C$1000,"D","0" )) <= 30150)*(Sheet1!$E$5:$E$1000),0)

Once again thanks for your help, which is most appreciated