Three things 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
- TOTAL PER SECTOR - should do a sum in column J:J based on sector
- TOTAL PER STOCK TYPE - should do a sum based on stock type > column C:C in column M:M based on type
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
CodePudding user response：
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)
CodePudding user response：
You may as well try:
Number of shares:
=SUMIFS($E$2:$E$7,$D$2:$D$7,$H$2:$H$7)
Total per Sector:
=SUMIFS($F$2:$F$7,$D$2:$D$7,$H$2:$H$7)
Total per Stock type:
=SUMIF(C:C, $L2, F:F)
On all three scenarios you need to drag down the formula for it to display the results.