Results 1 to 6 of 6

Thread: SumIf Formula Addition Based On AlphaNumeric Criteria

  1. #1
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13

    SumIf Formula Addition Based On AlphaNumeric Criteria

    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
    Attached Files Attached Files

  2. #2
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    I am little bit confused what you have mentioned in requirement. Please make it more understandable. Example would be the best way.

  3. #3
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13
    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
    Last edited by Howardc; 04-03-2013 at 12:47 PM.

  4. #4
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    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
    Last edited by LalitPandey87; 04-03-2013 at 03:00 PM.

  5. #5
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13
    Thanks for the help. This much appreciated

  6. #6
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13
    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

Similar Threads

  1. Replies: 7
    Last Post: 05-15-2013, 02:56 PM
  2. Replies: 7
    Last Post: 05-15-2013, 07:55 AM
  3. Replies: 4
    Last Post: 08-11-2012, 10:50 PM
  4. Lookup Based on Criteria
    By maruthi in forum Excel Help
    Replies: 2
    Last Post: 02-15-2012, 08:49 AM
  5. Update data from closed workbook with sumif formula
    By leopaulc in forum Excel Help
    Replies: 4
    Last Post: 11-02-2011, 02:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •