SumIf Formula Addition Based On AlphaNumeric Criteria

[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

[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/sum...eric-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