CODEWITHSUNDEEP
excelexcel-formula
Mamed Aliyev
Mamed Aliyev

Reputation: 191

how to calculate specific cells in excel

I have excel sheet which cells contain both string and numeric values. I have a problem calculating numeric values. I need a function that will check cells in given range and calculate(sum) numeric values. Result have to be : 8+4+10+12+12+5=51 given range

To get 8 and 4 values from 8/4b and 8b/4 I use this function: 

=IF(LEN(L27)=4,IF(ISNUMBER(SEARCH("b",LEFT(L27,2))),RIGHT(L27,1),LEFT(L27,1)),L27)

This function works well, but
1) it returns 8 and 4 values as char, not as number. So SUM function gives error.
2) how can I do the function to check cells in range and SUM them under those conditions (conditions in my function above)

thanks in advance

Upvotes: 1

Views: 508

Answers (3)

Mrig
Mrig

Reputation: 11702

I am assuming the data shown in your question is from H27 to Q27.
Use below formula to accomplish what you are looking for:

=SUM(IFERROR(VALUE(IF(LEN(H27:Q27)=4,IF(ISNUMBER(SEARCH("b",LEFT(H27:Q27,2))),RIGHT(H27:Q27,1),LEFT(H27:Q27,1)),H27:Q27)),0))

This is an array formula so commit the formula by Ctrl+Shift+Enter.

Just to let you know, to convert a char to number you can use VALUE function as:

=VALUE(IF(LEN(L27)=4,IF(ISNUMBER(SEARCH("b",LEFT(L27,2))),RIGHT(L27,1),LEFT(L27,1)),L27))

Upvotes: 2

Sun
Sun

Reputation: 762

How about this:

=IF(LEN(L27)=4,IF(ISNUMBER(SEARCH("b",LEFT(L27,2))),VALUE(RIGHT(L27,1)),VALUE(LEFT(L27,1))),L27)

Edit:

To put this all into one formula, I could only come up with this monster:

{=SUMPRODUCT(--(LEN(I27:N27)=4),--(ISNUMBER(SEARCH("b",LEFT(I27:N27,2)))),IF(ISNUMBER(VALUE(RIGHT(I27:N27,1))),VALUE(RIGHT(I27:N27,1)),0))+SUMPRODUCT(--(LEN(I27:N27)=4),--(ISNUMBER(SEARCH("b",RIGHT(I27:N27,2)))),IF(ISNUMBER(VALUE(LEFT(I27:N27,1))),VALUE(LEFT(I27:N27,1)),0))+SUM(I27:N27)}

This is an array formula, so you need to enter it with Ctrl+Shift+Enter. These brackets {} will then appear automatically. Do not enter them by hand.

As for the range, change I27:N27 to your range.

Upvotes: 1

Sixthsense
Sixthsense

Reputation: 1975

Try this...

=IFERROR(--IF(LEN(L27)=4,IF(ISNUMBER(SEARCH("b",LEFT(L27,2))),RIGHT(L27,1),LEFT(L27,1)),L27),0)

Upvotes: 1

Related Questions