How to Combine Two Formulas in Excel (3 Ways)

Here is an overview of combining two formulas in Excel.

How to Combine 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)

2-Using Ampersand Symbol to Combine Two Different Formulas


Case 1.2 – Use the CONCATENATE Function

  • Insert the following in cell D18.

=CONCATENATE("Average: ", AVERAGE(E5:E14), ", Total: ", SUM(E5:E14))

3-Use the CONCATENATE function to combine the AVERAGE and SUM functions

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")

4-Applying SUMIFS function to calculate conditional sales

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")

5-Combining IF and AND functions to check the sales status

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")

6-Nesting SUBSTITUTE Function

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:

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo