Totaling columns based on multiple criteria. SUMIF? SUMIFS? INDEX/MATCH?

[iamanexcelnoob]

hi all, i am looking to analyze a large set of data and to do so have developed a sort of basic dashboard.

in said dashboard, there is a dropdown (data validation list) with a month name that corresponds to the data set i am analyzing and in which the months are listed horizontally across the x axis.

i want to sum these columns based on one or two criteria in other columns of the data set.

for visual reference, let's say my data set looks as such:

Product	Jan	Feb	Mar	Apr
Orange	1	2	3	1
Pear	2	3	1	2
Orange	1	4	1	3
Pear	2	3	2	1
Apple	2	4	1	3
Apple	3	2	3	1
Orange	4	2	1	2
Pear	2	1	4	1
Orange	1	3	0	1

<tbody>
</tbody>

and my dashboard looks like the below with a dropdown box above for the month:

Product	Total (based on dropdown)
Apple
Orange
Pear

<tbody>
</tbody>

thanks in advance for any and all assistance!

 

[iamanexcelnoob]

sumif + index/match ALMOST works except it's only bringing back just the month in question. i.e. Mar only brings back Mar data not Jan-Mar.

thanks again!

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E
1	Product	Jan	Feb	Mar	Apr
2	Orange	1	2	3	1
3	Pear	2	3	1	2
4	Orange	1	4	1	3
5	Pear	2	3	2	1
6	Apple	2	4	1	3
7	Apple	3	2	3	1
8	Orange	4	2	1	2
9	Pear	2	1	4	1
10	Orange	1	3	0	1
11
12
13
14
15	Product	Mar
16	Apple	15
17	Orange	23
18	Pear	20
Data

Cell Formulas
Range	Formula
B16	=SUMPRODUCT(($A$2:$A$10=A16)*(MONTH($B$1:$E$1&1)<=MONTH($B$15&1)),$B$2:$E$10)

 

[iamanexcelnoob]

How about

Excel 2013/2016

	A	B	C	D	E
1	Product	Jan	Feb	Mar	Apr
2	Orange	1	2	3	1
3	Pear	2	3	1	2
4	Orange	1	4	1	3
5	Pear	2	3	2	1
6	Apple	2	4	1	3
7	Apple	3	2	3	1
8	Orange	4	2	1	2
9	Pear	2	1	4	1
10	Orange	1	3	0	1
11
12
13
14
15	Product	Mar
16	Apple	15
17	Orange	23
18	Pear	20

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Data​

Worksheet Formulas Cell Formula B16 =SUMPRODUCT(($A$2:$A$10=A16)*(MONTH($B$1:$E$1&1)<=MONTH($B$15&1)),$B$2:$E$10) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

that worked perfectly. great stuff, thank you!

 

[Fluff]

You're welcome & thanks for the feedback