Combination of IF conditions

Hi, all

I have a sheet where I would like to insert conditions in the worksheet. I'm not sure how to combine conditions.

I have three conditions:

1. If LEN < 179 in one cell, it will equals to 1 or else it will equals to 2
2. If the value in cell is 0, it will equals to 0.5
3. If the cell is empty, no need to sum it up

The equivalences will have to sum up. These conditions will have to be applied to A2:A6. Is it possible to create a formula? Hereby I attached an example of Excel file.

Thanks in advance for the help!

hi there. you didn't input what you are expecting to see. you may look at my signature and come up with an illustration if i got it wrong. my guess is:

Formula:

Please Login or Register  to view this content.

A2 = 2
A4 = 0.5
A5 = 1
total = 3.5

or can try
=SUMPRODUCT(LOOKUP(LEN(A2:A6),{0,1,180},{0,1,2})-(A2:A6<>"")*(A2:A6=0)*0.5)