Search code examples
google-sheetslambdagoogle-sheets-formulaarray-formulasgoogle-finance

Sum Values Based On Other Column Value

I am trying to do a formula for:

  • NUMBER OF SHARES per sector - should count all shares from column E:E in column I:I based on sector

enter image description here

TABLE:

SYMBOL NAME TYPE SECTOR OWNED SHARES SHARES PRICE SECTOR NUMBER OF SHARES TOTAL PER SECTOR TYPE TOTAL PER STOCK TYPE
ABML American Battery Technology Co Growth - 100 79 - Growth
BABA Alibaba Group Holding Ltd - ADR Growth Consumer Cyclical 200 12574 Consumer Cyclical Dividend
BAC Alibaba Group Holding Ltd - ADR Dividend Financial 1000 35460 Financial
LI Li Auto Inc Growth Consumer Cyclical 300 4791 Energy
MMP Magellan Midstream Partners, L.P. Dividend Energy 10000 515700
MPLX MPLX LP Dividend Energy 20000 662000

DEMO

https://docs.google.com/spreadsheets/d/1sBVb29p0yYcn3-CI3TvG6noN8AQPE2zxDCKojUcb2Nw/edit?usp=sharing

Thank you

I tried =sumif(D:D; H2; F:F) it works, but I wanted this function to be repeated for every value in column H once there is a new value.

Solution

  • Here is your formula for I2, then copy it down however far you need it:

    =SUMIF(D:D,$H2,E:E)

    Here is your formula for J2, then copy it down however far you need it:

    =SUMIF(D:D,$H2,F:F)

    And finally, your formula for M2, then copy it down however far you need it:

    =SUMIF(C:C, $L2,F:F)