Summing large data sets with multiple criteria

[tgilli]

	A	B	C	D
1		11/11/14	12/12/14	1/13/15
2	Carl	$4	$0	$11
3	Jesse	$3	$0	$14
4	Jake	$0	$4	$0
5	Carl	$0	$16	$14

<tbody>
</tbody>

I need to sum a large data set based on a name and a date. I want the total of Carl's spending in 2014. The answer should be $20. I'm trying to use a sumif equation.

=SUMIFS(B2:D5,A2:A5,"Carl",B1:D1,">=1/1/2014",B1:D1,"<1/1/2015")

This doesn't seem to work. What can I do to determine this?

 

[gaz_chops]

Welcome to the forum.

Try

=SUMPRODUCT((A2:A5="Carl")*(B1:D1>=A8)*(B1:D1<A9)*(B2:D5))

A8= 1/1/14
A9= 1/1/15

 

[SpillerBD]

Do you have the source data, not the "Pivot Table" layout as imaged ?

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​
1​		11/11/2014​	12/12/2014​	1/13/2015​		carl
2​	Carl	$4 ​	$0 ​	$11 ​		2014​
3​	Jesse	$3 ​	$0 ​	$14 ​		20​
4​	Jake	$0 ​	$4 ​	$0 ​
5​	Carl	$0 ​	$16 ​	$14 ​

In F2 control+shift+enter, not just enter:

=SUM(IF($A$2:$A$5=F1,IF(YEAR($B$1:$D$1)=F2,$B$2:$D$5)))<strike></strike>