Combine Two Formulas in Separate Cells Into One Cell

0 votes

I have 850 cells in Excel and have been asked to consolidate two calculations into one cell. If one cell is referencing a certain cell in another worksheet, such as ='Acquisition'! G32 and the alternate formula look like this.

=SUMIFS('Inputs'!$I:$I,'Inputs'!$C:$C,$C6,'Inputs'!$D:$D,F$4)

The goal is to have one formula like

='Acquisition'!G32 + SUMIFS('Inputs'!$I:$I,'Inputs'!$C:$C,$C6,'Inputs'!$D:$D,F$4)

Can I develop some code or an Excel function to accomplish this? As the references are to highly particular cells, I am unable to drag my formulas once throughout my range of cells.

I have attempted a number of Excel functions, including Cell, Concatenate, Offset, and a few others, but I am unable to combine the formulas into a single cell. Each formula's output can only be entered into one cell.

The goal is to go from: =formula1 = formula2 -> =formula1 + formula2



Mar 26, 2023 in Others by narikkadan
• 63,720 points
649 views

1 answer to this question.

0 votes

Use the openpyxl module in Python to do that, using something like this:

import openpyxl

import os

# Change working directory
mydir = ("YOUR_PATH")
os.chdir(mydir)
    
fname = "Book1.xlsx" # the filename of the file

# Opening the workbook
wb = openpyxl.load_workbook(filename = fname, 
                            data_only = False) # False because we want formulas

ws_names = wb.sheetnames # getting the sheet names

ws = wb[ws_names[0]] # setting ws to be the first sheet

# loop over cells
for i in range(1, 9):
    
    # combine cell formulas and assign to new cell
    ws.cell(i,4).value = (ws.cell(i,2).value + 
                          " + " + 
                          ws.cell(i,3).value)[1:] # [1:] to remove the '='

# save updated workbook to new file
wb.save('NewBook.xlsx')
answered Mar 26, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 63,720 points
1,076 views
0 votes
1 answer
0 votes
1 answer

How to run two commands in one line in Windows CMD?

Hi@MD, You can use the Logical And operator ...READ MORE

answered Oct 5, 2020 in Others by akhtar
• 38,240 points
6,327 views
0 votes
1 answer

How to merge two cells in excel with same field name

Insert 2 new columns, G & H. Enter ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,720 points
1,346 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
818 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,720 points
1,666 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,720 points
1,494 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,720 points
1,129 views
0 votes
1 answer

Two different fonts in one cell using excel vba

Public Sub add_degree_symbol() ActiveCell.Value = ...READ MORE

answered Apr 10, 2023 in Others by Kithuzzz
• 38,010 points
724 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP