Summing non-numerical data

Question

I am trying to quantify non-quantifiable data; I have a few columns of data and I need to find a formula that converts the data to numbers and sums them.

Example: One of my columns has data points "high" "medium" and "low" while another column has dollar amounts that need to be assigned a single number based on the dollar range (i.e. 1-249,999 is assigned a "1", 250,000-4,999,999 is assigned a "2", etc.) and another column has non-sequential text that needs to be converted (FFP = 1, LH=2, etc.) I have tried a number of different functions but haven't found a formula yet that can both convert my data into numbers and also sum the data in the same cell/function.

Answer 1

Hi

You can add corresponding assist column to convert text to number, then get result by sum them. You can hide the column to make the view clear.

H2=IFERROR(IFS(D2="High",5,D2="Medium-High",4,D2="Medium",3,D2="Low-Medium",2,D2="Low",1),"")

I2=IFERROR(IFS(E2="High",5,E2="Medium-High",4,E2="Medium",3,E2="Low-Medium",2,E2="Low",1),"")

J2=IFERROR(IFS(F2="IAA",6,F2="Award FEE",5,F2="TO/DO",4,F2="IDIQ",3,F2="FFP - A&E",2,F2="FFP",1),"")

K2=IFERROR(IFS(AND(G2>0,G2<250000),1,AND(G2>=250000,G2<5000000),2,AND(G2>=5000000,G2<100000000),3,G2>=100000000,4),"")

Drag down to fulfill the column. 

Note: you can set your rules like FFP = 1, LH=2, by modify the formula. See IFS fucntion. 

Regards,

Eric