Sum with vertical and horizontal references

[atelian]

Hi All,

I'm trying to sum a range of data with multiple vertical and horizontal criteria. Below is a table with sample data. For example, I'd like to sum Facebook Impressions if the date is between 9/26 and 9/28. All of it needs to change dynamically based on reference cells.

[TABLE="width: 594"]
<colgroup><col><col span="8"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]26-Sep[/TD]
[TD="align: right"]27-Sep[/TD]
[TD="align: right"]28-Sep[/TD]
[TD="align: right"]29-Sep[/TD]
[TD="align: right"]30-Sep[/TD]
[TD="align: right"]1-Oct[/TD]
[TD="align: right"]2-Oct[/TD]
[/TR]
[TR]
[TD]Impressions[/TD]
[TD]Facebook[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]510[/TD]
[TD="align: right"]520[/TD]
[TD="align: right"]530[/TD]
[TD="align: right"]540[/TD]
[TD="align: right"]550[/TD]
[TD="align: right"]560[/TD]
[/TR]
[TR]
[TD]Clicks[/TD]
[TD]Facebook[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]Spend[/TD]
[TD]Facebook[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD]Impressions[/TD]
[TD]Twitter[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]510[/TD]
[TD="align: right"]520[/TD]
[TD="align: right"]530[/TD]
[TD="align: right"]540[/TD]
[TD="align: right"]550[/TD]
[TD="align: right"]560[/TD]
[/TR]
[TR]
[TD]Clicks[/TD]
[TD]Twitter[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]Spend[/TD]
[TD]Twitter[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]26

[/TD]
[/TR]
</tbody>[/TABLE]


Any thoughts? This one is killing me.

Thanks!

 

[Momentman]

Try like this

Excel 2010
	A	B	C	D	E	F	G	H	I
1			26-Sep	27-Sep	28-Sep	29-Sep	30-Sep	01-Oct	02-Oct
2	Impressions	Facebook	500	510	520	530	540	550	560
3	Clicks	Facebook	50	51	52	53	54	55	56
4	Spend	Facebook	20	21	22	23	24	25	26
5	Impressions	Twitter	500	510	520	530	540	550	560
6	Clicks	Twitter	50	51	52	53	54	55	56
7	Spend	Twitter	20	21	22	23	24	25	26
8
9
10	Impressions	Facebook	1530
11		26-Sep
12		28-Sep
Amos

Cell Formulas
Range	Formula
C10	=SUMPRODUCT(($A$2:$A$7=A10)*($B$2:$B$7=$B$10)*($C$1:$I$1>=$B$11)*($C$1:$I$1<=$B$12)*$C$2:$I$7)

[TABLE="width: 85%"]
<tbody></tbody>[/TABLE]


I didn't see initially that you wanted both impressions and Facebook for example, now i have and have modified the formula accordingly

 

[JoeMo]

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Hdr1	Hdr2	26-Sep	27-Sep	28-Sep	29-Sep	30-Sep	1-Oct	2-Oct
2	Impressions	Facebook	500	510	520	530	540	550	560
3	Clicks	Facebook	50	51	52	53	54	55	56
4	Spend	Facebook	20	21	22	23	24	25	26
5	Impressions	Twitter	500	510	520	530	540	550	560
6	Clicks	Twitter	50	51	52	53	54	55	56
7	Spend	Twitter	20	21	22	23	24	25	26
8
9	Hdr1	Hdr2	Date1	Date2	Sum
10	Impressions	Facebook	26-Sep	28-Sep	1530
11	Spend	Twitter	26-Sep	30-Sep	110
Sheet11