(apple numbers) Search query for adding values

Question

Im making a sheet to have control over my investments.

To do this I'm making one table overlooking my buying history, and one where all my investment holdings show.

STOCK SHEET

What I want to do is to search the history table and add up all stocks in one row automatically in the inventory table.

Example:

Select all "how many" values from "history" where stock is equal to aapl.

In php this would return an array (I guess) and then I could add up all the values and display them. But this is not php and I don't know what this type of action is called.

Does someone have a solution to this and know what this action is called?

Thank you.

Answer 1

Here is a basic Excel recreation of your sheet

Here is the formula in cell H2:

=SUM(FILTER($C:$C,$A:$A=G2))

You should be able to then copy that cell downwards.

If you move thhe formulas to another sheet they change slightly to include the reference to the source sheet:

=SUM(FILTER(Sheet1!$C:$C,Sheet1!$A:$A=A2))

The same thing can be done with a seperate workbook or external data source but that get very complex as compared to this questions ask.