[Solved] Error in totalling the cells include text along with numbers in Excel

[shminhas]

Dear Members!

I want to add the multiple cells having numbers along with text, but I'm getting #value error. Any member, please dig out the issue and resolve my problem. I am attaching the screenshot. Thanks you.

 

[shamimarita]

Hello Shminhas,

The formula you have used from our article is correct:
=SUM(SUBSTITUTE(D59," Kg","")+0)

However, you're getting a #VALUE! error because this formula needs to be entered as an array formula or modified to work correctly with array inputs. The standard SUBSTITUTE() function does not handle ranges directly without array processing.

Here are two solutions:

Solution 1: Use SUMPRODUCT to handle arrays:
Replace your formula with this:
=SUMPRODUCT(--SUBSTITUTE(D59," Kg",""))

Solution 2: Array Formula (Excel 365 or Excel 2021 onwards):
You can also use your original formula but confirm it by pressing Ctrl+Shift+Enter after typing it, like this:
=SUM(SUBSTITUTE(D59," Kg","")+0)
Then press Ctrl+Shift+Enter instead of just Enter.

Explanation:

• SUBSTITUTE() removes the " Kg" text from each cell.
• -- or +0 converts the resulting text into numbers.
• SUMPRODUCT() or array formulas can correctly handle multiple cell inputs directly.

 

[shminhas]

But there is an issue; suppose some cell entries are with "Kg" and some are other than "Kg" in the selected range; let us say "NA". But we want to sum up only cell entries with "Kg" , means ignore the other than "Kg" cell entries so how can we do it?

 

[shamimarita]

Hello Shminhas,

If your range contains some cells with "Kg" and others with "NA" or other non-numeric text, you need to modify the formula to ignore non-numeric values while summing only the cells that contain "Kg".

Use the following SUMPRODUCT formula to only sum values that contain "Kg":
=SUMPRODUCT(--ISNUMBER(SEARCH("Kg",D59)) * SUBSTITUTE(D59," Kg",""))

Explanation:

• SEARCH("Kg",D59) checks if "Kg" is present in each cell.
• ISNUMBER(SEARCH("Kg",D59)) returns TRUE for cells containing "Kg" and FALSE otherwise.
• SUBSTITUTE(D59," Kg","") removes "Kg" from the numbers.
• Multiplying by --ISNUMBER(...) ensures only valid numbers with "Kg" are included in the sum.
• SUMPRODUCT(...) sums only the filtered numeric values.

Example:
If your D59 contains:
25 Kg
10 Kg
NA
7 Kg
30 Kg
The formula will correctly sum 25 + 10 + 7 + 30 = 72 while ignoring "NA".

This method ensures that only cells containing "Kg" are summed, ignoring any other text entries like "NA".

 

[shminhas]

Hello Shminhas,

If your range contains some cells with "Kg" and others with "NA" or other non-numeric text, you need to modify the formula to ignore non-numeric values while summing only the cells that contain "Kg".

Use the following SUMPRODUCT formula to only sum values that contain "Kg":
=SUMPRODUCT(--ISNUMBER(SEARCH("Kg",D59)) * SUBSTITUTE(D59," Kg",""))

Explanation:

• SEARCH("Kg",D59) checks if "Kg" is present in each cell.
• ISNUMBER(SEARCH("Kg",D59)) returns TRUE for cells containing "Kg" and FALSE otherwise.
• SUBSTITUTE(D59," Kg","") removes "Kg" from the numbers.
• Multiplying by --ISNUMBER(...) ensures only valid numbers with "Kg" are included in the sum.
• SUMPRODUCT(...) sums only the filtered numeric values.

Example:
If your D59 contains:
25 Kg
10 Kg
NA
7 Kg
30 Kg
The formula will correctly sum 25 + 10 + 7 + 30 = 72 while ignoring "NA".

This method ensures that only cells containing "Kg" are summed, ignoring any other text entries like "NA".

 

[shminhas]

Getting #value Error... after using the same formula which you mentioned.

 

[shamimarita]

Hello Shminhas,

Use the following formula:
=SUM(IF(ISNUMBER(SEARCH("Kg",A1:A5)), --SUBSTITUTE(A1:A5," Kg",""), 0))

 

[shminhas]

Hello Shminhas,

Use the following formula:
=SUM(IF(ISNUMBER(SEARCH("Kg",A1:A5)), --SUBSTITUTE(A1:A5," Kg",""), 0))

View attachment 1746

Thank you, ma'am; you are amazing

 

[shamimarita]

You are most welcome. Keep Exploring Excel with ExcelDemy. Keep helping each other to make this community stronger.