Need to calculate letters & numbers, but ignore the letters

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:

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

1. 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,"")))))
``````

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

2. 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,"")))))
``````