Sum Values Based On Other Column Value

Question

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

TABLE:

SYMBOL	NAME	TYPE	SECTOR	OWNED SHARES	SHARES PRICE	SECTOR	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

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.

Ask only one question per post. See https://meta.stackoverflow.com/questions/284236/why-is-can-someone-help-me-not-an-actual-question/284237#284237 — TheMaster, Oct 25 '22 at 20:38
So let me see if I understand this right. The Number of Shares is the sum of the Owned Shares column that matches the sector? The Total per Sector is the sum of the Shares Price column that also matches the sector? And the Total Per Stock Type is the sum of the Shares Price column that match the Type column? — LASheets, Oct 25 '22 at 20:54
@Gabriel Carballo Updated my question with ```=sumif(D:D; H2; F:F)``` :-) — vohratom, Oct 26 '22 at 09:26

score 1 · Accepted Answer · answered Oct 25 '22 at 21:01

1

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)

answered Oct 25 '22 at 21:01

LASheets

Hi @LASheets thank you for your answer, yes, **SUMIF** works, however, I wanted this function to be repeated for every value in column **H** once there is a new value – vohratom Oct 26 '22 at 09:28
1

Try this one @vohratom. You'll have to change the formula for each of your columns, but this should work for you. The important piece is using the SUMIF inside of the ARRAYFORMULA. The IF(ISBLANK()) part is just to eliminate a whole list of 0s for blank cells in H. =ArrayFormula(IF(ISBLANK(H2:H),,SUMIF($D$2:$D,$H$2:$H,$E$2:$E))) – LASheets Oct 26 '22 at 16:03

score 1 · Answer 2 · answered Oct 25 '22 at 21:06

1

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.

answered Oct 25 '22 at 21:06

Gabriel Carballo

Hi @Gabriel Carballo thank you, I answered in a similar way to **LASheets** in the comment above this one - in short, I just wanted this function to be repeated once there is a new line in column **** without having to copy-paste the function :-) – vohratom Oct 26 '22 at 09:30

2 Answers2