I assume that the blanks are the problem because when you use =VALUE("") you get an error. Try concatenating a zero to the start of SUBSTITUTE so that blanks become zeroes
=SUMPRODUCT(VALUE(0&SUBSTITUTE(A1:A8,"*","")))
or you can use +0 in place of VALUE, i.e.
=SUMPRODUCT((0&SUBSTITUTE(A1:A8,"*",""))+0)
Top answer 1 of 2
6
I assume that the blanks are the problem because when you use =VALUE("") you get an error. Try concatenating a zero to the start of SUBSTITUTE so that blanks become zeroes
=SUMPRODUCT(VALUE(0&SUBSTITUTE(A1:A8,"*","")))
or you can use +0 in place of VALUE, i.e.
=SUMPRODUCT((0&SUBSTITUTE(A1:A8,"*",""))+0)
2 of 2
0
Try
=SUM(VALUE(SUBSTITUTE(A1:A8,"*","")))
and enter it with Ctrl + Shift + Enter, instead of just Enter. This makes it an array formula, and it will treat the A1:A8 range as an array for the SUBSTITUTE() function. Thus, SUBSTITUTE() now evaluates each individual value in A1:A8 separately. VALUE() converts the text to numbers, and sum() adds all of them up.
Edit: The formula =SUMPRODUCT(VALUE(SUBSTITUTE(A1:A8,"*",""))) seems to be working for me. (Normal formula, not an array formula).
Reddit
reddit.com › r/excel › can i nest substitute functions inside of a sum?
r/excel on Reddit: Can I nest SUBSTITUTE functions inside of a SUM?
October 14, 2023 -
Like so?
=SUM(SUBSTITUTE(H3;"μg";"");SUBSTITUTE(J3;"µg";""))
The problem is I want to add values with units in them, without having to do the extra step of creating a separate coloumn where every single value is repeated without the unit just so they can be added up. I thought maybe I could be sneaky and substitute the unit out of the data, add them together and then spit it out. But I guess not. The formula above currently returns the #VALUE! error.
Top answer 1 of 7
8
Why not use Custom Format and add the "μg" , this keeps your figures as numbers which can be Summed. https://pixeldrain.com/u/cWhdBUiR
2 of 7
3
You can add the VALUE function (which converts numerals (text) into numbers) =SUM(VALUE(SUBSTITUTE(H3;"μg";"")); VALUE(SUBSTITUTE(J3;"µg";"")))
MrExcel
mrexcel.com › forums › question forums › excel questions
Sum and Substitute Help | MrExcel Message Board
June 10, 2014 - <tbody> </tbody> Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8 · Last edited: Jun 10, 2014 · Upvote 0 · J · Joined · Jun 10, 2014 · Messages · 10 · Jun 10, 2014 · #4 · Domenic said: Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER... =SUM(IF(LEN(G14:M14)>0,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(G14:M14),"S",""),"V",""),"P","")+0)) Hope this helps!
Ablebits
ablebits.com › ablebits blog › excel › find & replace › using excel replace and substitute functions - formula examples
Using Excel REPLACE and SUBSTITUTE functions - formula examples
March 21, 2023 - An Excel formula can only change the value of the cell in which it is written. For your task, you need to write VBA code ... Hi, very nice thing you got here going on. I've encountered a little problem with the substitute function. Assuming this is the formula I use in my reference cell S2: =SUM(IF(AY2=AZ2;1;0)+IF(AY3=AZ3;1;0) And then I use in another cell: =SUBSTITUTE(SUBSTITUTE(S2;"AY";"BG"),"AZ","BH") to keep the formula the same, but to have every AY substituted by BG, and every AZ by BH.
ExtendOffice
extendoffice.com › home › office tips › excel tips
How to correctly sum numbers with units in a range in Excel?
Select a blank cell for your result and enter the following formula: =SUM(SUBSTITUTE(B2:B7, "kg", "") +0) After typing the formula, confirm it by pressing Ctrl + Shift + Enter together (on some systems, this is required for array formulas), so Excel recognizes it as an array operation.
CodePal
codepal.ai › formula generator › excel formula: sum cells with substitute
Excel Formula: Sum Cells with SUBSTITUTE - CodePal
September 12, 2023 - If an error occurs, such as when ... and returns either the converted numeric values or 0. Finally, the SUM function is used to sum the resulting values....
Stack Overflow
stackoverflow.com › questions › 50175658 › sumproduct-or-sumif-with-substitute-and-criteria
excel - Sumproduct or sumif with substitute and criteria - Stack Overflow
excel-formula · Share · Improve this question · Follow · edited May 4, 2018 at 13:32 · Scott Craner · 154k1010 gold badges5252 silver badges8888 bronze badges · asked May 4, 2018 at 13:03 · Guillaume · 1 2 · Perhaps this as an array formula (must be confirmed with Ctrl+Shift+Enter instead of just Enter): =SUM(IF(ISNUMBER(SEARCH("RO",A1:E1)),--SUBSTITUTE(A1:E1,"RO",""),0)) tigeravatar – tigeravatar ·
wmfexcel
wmfexcel.com › 2015 › 04 › 11 › sum-a-range-of-number-end-with-k-input-as-text
Sum a range of number end with “k” | wmfexcel
April 11, 2015 - ... It simply means “In the text, look for the old text we specify and then substitute it with the new text we want.” In our example, it looks into “1.5k“, looks for “k“, and replaces it with nothing.
MrExcel
mrexcel.com › forums › question forums › excel questions
SUBSTITUTE in Sumifs | MrExcel Message Board
October 6, 2017 - Re: how to use SUBSTITUTE in the Sumifs Peter, Cool!! it is work, and thank you so much. One more question, I would like to use one formula such that sum all the deals that the group contains "8000A". There are 2 sheets.
ExcelDemy
exceldemy.com › home › excel formulas › how to sum cells with text and numbers in excel
How to Sum Cells with Text and Numbers in Excel - ExcelDemy
May 30, 2024 - Home » Excel Formulas » How to Sum Cells with Text and Numbers in Excel ... The SUBSTITUTE function can separate the text from the numeric values.
Super User
superuser.com › questions › 1050853 › is-it-possible-to-have-a-sum-of-text-replaced-cells-in-openoffice-org-calc
microsoft excel - Is it possible to have a sum of text REPLACE'd cells in openoffice.org calc? - Super User
March 9, 2016 - =SUMPRODUCT(VALUE(REPLACE(A17:A18;1;3;''))) No "Ctrl+Shift+Enter" required. Share · Improve this answer · Follow · edited Mar 10, 2016 at 1:00 · answered Mar 10, 2016 at 0:11 · insaner · 38522 silver badges1616 bronze badges · Add a comment | Start asking to get answers · Find the answer to your question by asking. Ask question · Explore related questions · microsoft-excel ·
Stack Overflow
stackoverflow.com › questions › 31657406 › sum-with-substitute-ignore-and-a-dynamic-range
excel - Sum with substitute, ignore and a dynamic range - Stack Overflow
July 21, 2016 - I came pretty close to solving my problem with this answer given by "barry houdini" But I run into problems incorporating it to my needs. =SUMPRODUCT(VALUE(0&SUBSTITUTE(A1:A8,"*",""))) Edi...
Excel Forum
excelforum.com › excel-general › 736328-substitute-and-sum-values-from-multiple-cells.html
Thread: Substitute and sum values from multiple cells
Hi there, I have a row of cells some of which containing the letter 'H', and I want to be able to count the number of H's. I believed I could do this with either the substitute or vlookup function such as: sum(substitute($A1:$A5,'H','1')); But these functions do not appear to work for a range ...
Excel Tip
exceltip.com › home › summing
How to Sum Values in a Range Containing Redundant Spaces in Excel
January 23, 2021 - How to Sum Multiple Columns with Condition: Returns the SUM of values across multiple columns having condition in excel. Excel REPLACE vs SUBSTITUTE function: The REPLACE and SUBSTITUTE functions are the most misunderstood functions. To find and replace a given text we use the SUBSTITUTE function.
Super User
superuser.com › questions › 1356469 › replacement-for-sum-range-in-sumifs-formula-that-can-match-automatically-with
microsoft excel - Replacement for 'sum_range' in SUMIFS formula that can match automatically with source data header - Super User
Do you want to get the result as shown? imgur.com/a/hBoEqPr Try this formula: =SUM(INDIRECT(SUBSTITUTE(LEFT(ADDRESS(1,MATCH(C5,$4:$4,0)),3),"$","")&SUMPRODUCT(($G$4:$K$7=$C$1)*ROW($4:$7))&":"&SUBSTITUTE(LEFT(ADDRESS(1,MATCH(C5,$4:$4,0)),3),"$","")&SUMPRODUCT(($G$4:$K$7=$C$2)*ROW($4:$7)))) Lee – Lee ·
Microsoft Support
support.microsoft.com › en-us › excel › functions › sum-function
SUM function | Microsoft Support
For example, to get the difference between 8:00 AM - 12:00 PM for payroll purposes you would use: =("12:00 PM"-"8:00 AM")*24, taking the end time minus the start time. Note that Excel calculates times as a fraction of a day, so you need to multiply by 24 to get the total hours. In the first example we're using =((B2-A2)+(D2-C2))*24 to get the sum of hours from start to finish, less a lunch break (8.50 hours total).
Quora
quora.com › Is-there-a-way-to-have-the-sum-of-multiple-cells-replace-the-value-of-an-existing-cell-within-that-function
Is there a way to have the sum of multiple cells replace the value of an existing cell within that function? - Quora
Answer (1 of 2): It is possible, providing you do something like this: I have these values in cells A6:A10 16753 14808 10644 1390 4865 Their total is 48460 in cell C10 I have the formula =SUMIF(A6:A10," 50000",A6:A10) which gives me 48460 in cell D10, I have this formula =IF(SUM(A6:A10)...
Microsoft Support
support.microsoft.com › en-us › office › substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332
SUBSTITUTE function | Microsoft Support
Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.
Quora
quora.com › What-is-an-alternative-to-sumifs-in-Excel
What is an alternative to sumifs in Excel? - Quora
Answer: Before the formula SUMIF() was invented what I did was put the IF() part of the equation in a new column and sum that column. So, for example, if I had a bunch of sales areas, the traditional North, South, West, East in column A and the daily sales totals in column B, I could put the foll...
YouTube
youtube.com › watch
How to Calculate SUM of Every Alternative Cells in Excel - YouTube
Learn how to calculate the SUM of values from every alternative cells in Microsoft Excel.Download the workbook file for practice from the link below.Workbook...
Published: February 17, 2024