Formula/Function for adding cells that have are less than values

Question

Can someone assit me with which formula to use to add cells together that contain the less than symbol for numbers from lab analysis results? I have attached a snip it for reference.

Need to know ASAP.

MPTotal each Row for four columns

m_tarler · Answer

try:=SUM(--SUBSTITUTE(A1:A5,"&lt;",""))where A1:A5 is the range you need to sumalternatively&nbsp;=SUM(IF(ISNUMBER(A1:A5),A1:A5,--MID(A1:A5,2,99)))in this case it will remove the 1st character if it isn't a number so "&gt;999" would also become a number (999)

oliverscheurich · Answer

MonicaPeppin&nbsp;=SUMPRODUCT(--(SUBSTITUTE(D2:G2,"&lt;","")))&nbsp;This formula works in my sample file.&nbsp;

patrick2788 · Answer

MonicaPeppin&nbsp;A solution with BYROW:&nbsp;=LET(
    numbers, 1 * TEXTAFTER(matrix, "&lt;", , , , matrix),
    BYROW(numbers, SUM)
)Matrix being C7:G11&nbsp;&nbsp;&nbsp;

Forum Discussion

Formula/Function for adding cells that have are less than values

3 Replies

Resources