Sum all values for all ocurrences when multiple matches

[bais]

Hi

I've been struggling with summing up values in a dataset where there's multiple occurences of the category I need to sum for.
Ive made a small dataset illustrating the issue.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

Q

1

J

F

M

A

M

J

J

A

S

O

N

D

Accumulated to date

2

Apples

1​

5​

3​

6​

9​

2​

3​

1​

Apples

3

Oranges

2​

3​

4​

2​

1​

3​

4​

5​

Oranges

4

Bananas

3​

1​

5​

7​

2​

6​

8​

0​

Bananas

5

Grapes

1​

3​

5​

4​

7​

2​

6​

0​

Grapes

6

Lemons

3​

1​

5​

7​

2​

6​

8​

0​

Lemons

7

Apples

0​

4​

9​

6​

9​

5​

3​

1​

8

Grapes

1​

5​

3​

6​

9​

2​

3​

1​

So what I'm trying to achieve is having all values, for all month, for all categories summed and written in Q2. In the example Apple and Grape both occurs two times and that's the headache.
So I need the summed values to consider all values for all occurences of a category
NB: The categories can occur on any place in the set, so it's not given that it's A2 and A7 as it's the case for Apple in the example dataset

I had to sort it out for now by adding a SUM column after the dataset (ie. after M) And calculate the summed values using SUMIF in column Q
But I'm sure there's a more elegant way of achieving it, ie. without the SUM column.

So I turn to the experts on this forum, which I stumbled across trying to achieve the above
I found several examples which was sort of similar but not exactly the same, so I tried a little with Array functions, SUMPRODUCT and other "exotic" formulas (at least they were to me)

Thanks so much in advance for your time and assistance

/bais

 

[Fluff]

Hi & welcome to MrExcel.
How about
=SUMPRODUCT(($A$2:$A$8=O2)*($B$2:$M$8))

 

[bais]

Hi & welcome to MrExcel.
How about
=SUMPRODUCT(($A$2:$A$8=O2)*($B$2:$M$8))

Well hey presto!
I definitely came to the right place!
That was exactly what I couldn't get to work on my own

Thanks a whole bunch!
Have a nice day

 

[Fluff]

You're welcome & thanks for the feedback

 

[Peter_SSs]

If your Excel 365 has the FILTER function, then you could also use this.

20 04 16.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		J	F	M	A	M	J	J	A	S	O	N	D			Accumulated to date
2	Apples	1	5	3	6	9	2	3	1						Apples	67
3	Oranges	2	3	4	2	1	3	4	5						Oranges	24
4	Bananas	3	1	5	7	2	6	8	0						Bananas	32
5	Grapes	1	3	5	4	7	2	6	0						Grapes	58
6	Lemons	3	1	5	7	2	6	8	0						Lemons	32
7	Apples	0	4	9	6	9	5	3	1
8	Grapes	1	5	3	6	9	2	3	1
SUM

Cell Formulas
Range	Formula
P2:P6	P2	=SUM(FILTER(B$2:M$8,A$2:A$8=O2))

 

[bais]

If your Excel 365 has the FILTER function, then you could also use this.

20 04 16.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		J	F	M	A	M	J	J	A	S	O	N	D			Accumulated to date
2	Apples	1	5	3	6	9	2	3	1						Apples	67
3	Oranges	2	3	4	2	1	3	4	5						Oranges	24
4	Bananas	3	1	5	7	2	6	8	0						Bananas	32
5	Grapes	1	3	5	4	7	2	6	0						Grapes	58
6	Lemons	3	1	5	7	2	6	8	0						Lemons	32
7	Apples	0	4	9	6	9	5	3	1
8	Grapes	1	5	3	6	9	2	3	1
SUM

Cell Formulas
Range	Formula
P2:P6	P2	=SUM(FILTER(B$2:M$8,A$2:A$8=O2))

Yeah, thanks that worked too

 

[Peter_SSs]

Yeah, thanks that worked too

BTW, if it is possible that a fruit in column O does not exist in column A, then my formula would actually need to be

=SUM(FILTER(B$2:M$8,A$2:A$8=O2,0))