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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


and my dashboard looks like the below with a dropdown box above for the month:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Total (based on dropdown)[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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
Product	Jan	Feb	Mar	Apr
Orange
Pear
Orange
Pear
Apple
Apple
Orange
Pear
Orange
Product	Mar
Apple
Orange
Pear

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Data​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B16[/TH]
[TD="align: left"]=SUMPRODUCT(($A$2:$A$10=A16)*(MONTH($B$1:$E$1&1)<=MONTH($B$15&1)),$B$2:$E$10)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

that worked perfectly. great stuff, thank you!

 

[Fluff]

You're welcome & thanks for the feedback