Need to calculate letters & numbers, but ignore the letters

navshark 1 Reputation point
2021-09-01T20:07:29.943+00:00

I'm trying to create a function in Excel 2010 to calculate a total based on the entries in cell range B4:AF4. Each cell entry will consist of a letter followed by a number (e.g. A2), but some cells may contain multiple entries (e.g. A2, S6). I need to create a formula that will ignore the letters in the range and only add to a Total cell based on the following info:

1-5 will add 1, 6-8 will add 2, 9-11 will add 3, and 12-14 will add 4

So an entry of "A2" will only add 1 to the total, but "A2, S6" would add 3.

If this is in any way confusing, I apologize. I'll be glad to give additional info to clarify what I'm trying to do.

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,670 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Emi Zhang-MSFT 22,951 Reputation points Microsoft Vendor
    2021-09-02T09:52:53.72+00:00

    Hi @navshark ,
    Based on your description, I created a sample, please refer to this support article:

    =IF(IFERROR(FIND(",",A1),"")="",IF(AND(1<=NUMBERVALUE((RIGHT(A1,LEN(A1)-1))),NUMBERVALUE((RIGHT(A1,LEN(A1)-1)))<=5),1,IF(AND(6<=NUMBERVALUE((RIGHT(A1,LEN(A1)-1))),NUMBERVALUE((RIGHT(A1,LEN(A1)-1)))<=8),2,IF(AND(9<=NUMBERVALUE((RIGHT(A1,LEN(A1)-1))),NUMBERVALUE((RIGHT(A1,LEN(A1)-1)))<=11),3,IF(AND(12<=NUMBERVALUE((RIGHT(A1,LEN(A1)-1))),NUMBERVALUE((RIGHT(A1,LEN(A1)-1)))<=14),4,"")))),IF(AND(1<=NUMBERVALUE(MID(A1,2,IFERROR(FIND(",",A1),"")-2)),NUMBERVALUE(MID(A1,2,IFERROR(FIND(",",A1),"")-2))<=5),1,IF(AND(6<=NUMBERVALUE(MID(A1,2,IFERROR(FIND(",",A1),"")-2)),NUMBERVALUE(MID(A1,2,IFERROR(FIND(",",A1),"")-2))<=8),2,IF(AND(9<=NUMBERVALUE(MID(A1,2,IFERROR(FIND(",",A1),"")-2)),NUMBERVALUE(MID(A1,2,IFERROR(FIND(",",A1),"")-2))<=11),3,IF(AND(12<=NUMBERVALUE(MID(A1,2,IFERROR(FIND(",",A1),"")-2)),NUMBERVALUE(MID(A1,2,IFERROR(FIND(",",A1),"")-2))<=14),4,""))))+IF(AND(1<=NUMBERVALUE(RIGHT(A1,(LEN(A1)-IFERROR(FIND(",",A1),"")-1))),NUMBERVALUE(RIGHT(A1,(LEN(A1)-IFERROR(FIND(",",A1),"")-1)))<=5),1,IF(AND(6<=NUMBERVALUE(RIGHT(A1,(LEN(A1)-IFERROR(FIND(",",A1),"")-1))),NUMBERVALUE(RIGHT(A1,(LEN(A1)-IFERROR(FIND(",",A1),"")-1)))<=8),2,IF(AND(9<=NUMBERVALUE(RIGHT(A1,(LEN(A1)-IFERROR(FIND(",",A1),"")-1))),NUMBERVALUE(RIGHT(A1,(LEN(A1)-IFERROR(FIND(",",A1),"")-1)))<=11),3,IF(AND(12<=NUMBERVALUE(RIGHT(A1,(LEN(A1)-IFERROR(FIND(",",A1),"")-1))),NUMBERVALUE(RIGHT(A1,(LEN(A1)-IFERROR(FIND(",",A1),"")-1)))<=14),4,"")))))  
    

    128682-470.png

    Just checking in to see if the information was helpful. Please let us know if you would like further assistance.


    If the response is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. Emi Zhang-MSFT 22,951 Reputation points Microsoft Vendor
    2021-09-15T01:56:44.777+00:00

    Hi @navshark ,
    It seems the NUMBERVALUE function is not worked in Excel 2010. I suggest you change the NUMBERVALUE function to VALUE function:

    =IF(IFERROR(FIND(",",A1),"")="",IF(AND(1<=VALUE((RIGHT(A1,LEN(A1)-1))),VALUE((RIGHT(A1,LEN(A1)-1)))<=5),1,IF(AND(6<=VALUE((RIGHT(A1,LEN(A1)-1))),VALUE((RIGHT(A1,LEN(A1)-1)))<=8),2,IF(AND(9<=VALUE((RIGHT(A1,LEN(A1)-1))),VALUE((RIGHT(A1,LEN(A1)-1)))<=11),3,IF(AND(12<=VALUE((RIGHT(A1,LEN(A1)-1))),VALUE((RIGHT(A1,LEN(A1)-1)))<=14),4,"")))),IF(AND(1<=VALUE(MID(A1,2,IFERROR(FIND(",",A1),"")-2)),VALUE(MID(A1,2,IFERROR(FIND(",",A1),"")-2))<=5),1,IF(AND(6<=VALUE(MID(A1,2,IFERROR(FIND(",",A1),"")-2)),VALUE(MID(A1,2,IFERROR(FIND(",",A1),"")-2))<=8),2,IF(AND(9<=VALUE(MID(A1,2,IFERROR(FIND(",",A1),"")-2)),VALUE(MID(A1,2,IFERROR(FIND(",",A1),"")-2))<=11),3,IF(AND(12<=VALUE(MID(A1,2,IFERROR(FIND(",",A1),"")-2)),VALUE(MID(A1,2,IFERROR(FIND(",",A1),"")-2))<=14),4,""))))+IF(AND(1<=VALUE(RIGHT(A1,(LEN(A1)-IFERROR(FIND(",",A1),"")-1))),VALUE(RIGHT(A1,(LEN(A1)-IFERROR(FIND(",",A1),"")-1)))<=5),1,IF(AND(6<=VALUE(RIGHT(A1,(LEN(A1)-IFERROR(FIND(",",A1),"")-1))),VALUE(RIGHT(A1,(LEN(A1)-IFERROR(FIND(",",A1),"")-1)))<=8),2,IF(AND(9<=VALUE(RIGHT(A1,(LEN(A1)-IFERROR(FIND(",",A1),"")-1))),VALUE(RIGHT(A1,(LEN(A1)-IFERROR(FIND(",",A1),"")-1)))<=11),3,IF(AND(12<=VALUE(RIGHT(A1,(LEN(A1)-IFERROR(FIND(",",A1),"")-1))),VALUE(RIGHT(A1,(LEN(A1)-IFERROR(FIND(",",A1),"")-1)))<=14),4,"")))))  
    

    132152-479.png


    If the response is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  3. Herbert Seidenberg 1,191 Reputation points
    2021-10-03T16:26:48.46+00:00
    0 comments No comments