[Solved] Use two formulas in one cell

[Pinkmen]

Hello there, I have a somewhat funky problem here.

I need to know if I can use =sumproduct() and =subtractif(), if that's a actual thing, in the same line of code.

Ill try my best to explain what I need done.

I have a spreadsheet with tons of data regarding bank statements, I have money in, money out, money transferred to other accounts.
I barely begun to add the new variable TRANSFER. I came up with having the Transfer money go into a pivot table, which states if that money went out or in to the certain account. After that pivot table I either subtract that value or add it onto the =sumproduct() that is being used as a main balance.

That way seems like too much, is there a way I could combine those two into one line of code that says something like.
In this bunch of data, add the same month together, if it says x to y subtract from x, if it says y to x add to x.

I hope this somewhat makes sense.. Ive looked online for videos on this sort of issue, but most tutorials are extremely basic..

[MrProgrammer]

You are more likely to get assistance if you attach a spreadsheet demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). Explain which cells on which sheets will be involved in this calculation. In terms of those cells, explain in English how you want the calculation to be done. What cell on what sheet will hold the result? I will not help further unless you attach a spreadsheet document.

I need to know if I can use =sumproduct() and =subtractif(), if that's a actual thing, in the same line of code.

SUMPRODUCT and IF are Calc functions. Subtraction is a Calc operator, written with a minus sign. You can use a combination of functions and operators in a Calc formula. Examples:
• The IF function can use the subtraction operator (subtract two numbers and pass the result to IF)
=IF(A2>20;B2-5;B2-1)
• The SUMPRODUCT function can be an operand for the subtraction operator (evaluate SUMPRODUCT and pass the result to the subtraction operator)
=SUMPRODUCT(A2:A9;B2:B9)-25
• Functions can be nested (evaluate AND and pass the result to IF)
=IF(AND(A2>=10;A2<20);0;B2)
• Operators can be combined (add the squares of two cells)
=A2^2+B2^2

You can use one formula in a cell. The formula supplies the cell's value. The formula cannot change a value in a different cell. The formula will begin with an equal sign before the first term. The last example shows that it is not necessary to use a function in a formula.
[Tutorial] Calc formula terms
[Tutorial] Order of Operations in Calc

I barely begun to add the new variable TRANSFER.

Calc does not have variables. Perhaps you mean that you have a cell, or a column of cells, or a row of cells, which contain transfer amounts. Your attachment should help make it clear what you mean. Explain what is your transfer data in your attachment.
[Tutorial] Ten concepts that every Calc user should know

[keme]

Are you using text input of type "from one_account to other_account" to determine when to add/subtract a given amount?

The variable you mention, is that one single named cell, a range (column or row) you named, a heading in your table which is autodetected and use as the name, or something else?

There are pitfalls with an approach to govern calculations by parsed textual input. Mostly connected with typos (if it is manual text input) and leading/trailing spaces (if the parsing step does not clean up the elements properly, or the row/column headings are not entered in consistent manner).

To advise on how to go about your approach is not really feasible unless you provide a sample file with what you have and rephrase your description to use references to the contents of that file. (Make a copy where you remove all sensitive/confidential content. This is a public place.)

[Pinkmen]

I was never informed if I could use two seperate formulas in one cell, however I personally found the solution to my problem.

Which was to add another variable onto the spreadsheet to be able to sort it out easier through the =sumproduct(;;;;;;;) command.

I had the idea that i could use a =sumif, and a =sumproduct, in the same cell. But idk. Anyways my problem with this is solved either or.

[keme]

I was never informed if I could use two seperate formulas in one cell,

You were told by MrProgrammer that ...

• You can only have one formula in a cell. You start your input with the equals sign to tell Calc that "here comes a formula".
• SUMPRODUCT() and IF() are referred to as "functions", not "formulas". You can use multiple functions within a formula.

MrProgrammer also provided several basic examples of how functions could be used in formulas.

however I personally found the solution to my problem.

Excellent!

We strive to help our fellow users to help themselves. "Fixed it myself" messages make me happy. It means that someone has been learning something.
A part of that "peer support" is also encouraging everyone to share their own solutions, for the benefit of others. I recognize that it is not always desirable to share too much. Maybe next time?

[Pinkmen]

Hey there! I completely misread everything lol.

The solution to my weird problem was adding a new Header on the Data sheet of mine. That way there is more information that the =sumproduct() can draw on. Adding a new header/variable means the more i can sort through the entirety of the data, and get the exact information I need out of it.

Sometimes the solutions are too simple, that they can be overlooked.

Im glad there is a place I can come to, in order to discuss any type of coding problem though! It really helps to chat with people who actually know these languages lol.