Here is an overview of combining two formulas in Excel.
How to Combine Two Formulas in Excel: 3 Ways
Method 1 – Two Different Formulas in a Single Cell in Excel
Case 1.1 – Using the Ampersand Symbol to Combine Two Formulas
- Insert the following formula in cell D18.
="Average: "&AVERAGE(E5:E14)&", Total: "&SUM(E5:E14)
Case 1.2 – Use the CONCATENATE Function
- Insert the following in cell D18.
=CONCATENATE("Average: ", AVERAGE(E5:E14), ", Total: ", SUM(E5:E14))
Formula Breakdown:
- The AVERAGE function calculates the average of the cell range E5:E14.
- The SUM function determines the total sales of the products.
- The CONCATENATE function combines the result of the AVERAGE and SUM functions.
Read More: How to Copy CONCATENATE Formula in Excel
Method 2 – Apply the SUMIFS Function
We will sum up the sales that are greater than $85,000.00 by applying the SUMIFS function and adding the average sales.
- Enter the following formula in cell D18.
=D16+SUMIFS(E5:E14,E5:E14,">85000")
Formula Breakdown:
- The SUMIFS function will sum up the sales that are greater than $85,000.00.
- Hence, the mathematical operator plus (+) symbol combines the result of the SUMIFS function with the value of the cell D16.
Read More: How to Concatenate Date/Day, Month, and Year in Excel
Method 3 – Nested IF and AND Functions
We’ll check the sales status of the sales representatives.
- In cell F5, use the following formula.
=IF(AND(E5>D5),"Accomplished","Not Accomplished")
Formula Breakdown:
- If the value of cell E5 is greater than the value of cell D5, the AND function will return TRUE otherwise FALSE.
- If the sales by Rozalie Freke is greater than D5 then the IF function will return Accomplished otherwise it returns Not Accomplished.
Read More: How to Concatenate Names in Excel
How to Use a Nested SUBSTITUTE Function in Excel
We’ll change a particular text from a specific position in Excel.
- Insert the following formula in cell D5 and AutoFill the formula.
=SUBSTITUTE(SUBSTITUTE(B5,"a","e"),"oo","o")
Formula Breakdown:
- Inner SUBSTITUTE function replaces “a” with “e”.
- Outer SUBSTITUTE function replaces “oo” with “o”.
Read More: How to Concatenate Date and Time in Excel
Things to Remember
- Use the “&” operator to combine text or results of formulas.
- Always double-check your formulas for accuracy and ensure correct cell references.
- Parentheses control the order of operations in nested formulas.
Frequently Asked Questions
How can I combine the results of two different formulas into one cell in Excel?
You can use the “&” operator to concatenate the results of two formulas. For example, if you have two formulas in cells A1 and B1, you can combine them with the formula “=A1&B1” in another cell to display their outputs together.
Is it possible to multiply the results of two formulas and display the combined result in another cell?
Absolutely! You can multiply the results of two formulas by referencing their cells in a new formula. For example, if you have formulas in cells A1 and B1, you can use the formula “=A1*B1” in another cell to display the combined multiplication result.
Can I use logical functions like AND or OR to combine the results of two formulas based on certain conditions?
Yes, you can use the logical functions AND and OR to combine the results of two formulas based on specific conditions. For example, to check if both A1 and B1 are greater than 10, you can use the formula “=AND(A1>10, B1>10)“.
Download the Practice Workbook
Related Articles:
- Excel VBA: Combine Date and Time
- Carriage Return in Excel Formula to Concatenate
- How to Combine Names in Excel with Space
- How to Combine Name and Date in Excel
Thank you so much – I was finally able to “visualize” how to nest two substitute formulas by following your example!
Hello Denise Groves,
You are most welcome. Glad to hear that our tutorial helped you to visualize nesting substitute formulas. Keep exploring Excel formulas with ExcelDemy!
Regards
ExcelDemy