Summing large data sets with multiple criteria

[tgilli]

[TABLE="class: grid, width: 350, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]11/11/14[/TD]
[TD]12/12/14[/TD]
[TD]1/13/15[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Carl[/TD]
[TD]$4[/TD]
[TD]$0[/TD]
[TD]$11[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jesse[/TD]
[TD]$3[/TD]
[TD]$0[/TD]
[TD]$14[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jake[/TD]
[TD]$0[/TD]
[TD]$4[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Carl[/TD]
[TD]$0[/TD]
[TD]$16[/TD]
[TD]$14[/TD]
[/TR]
</tbody>[/TABLE]
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]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td]

11/11/2014​

[/td][td]

12/12/2014​

[/td][td]

1/13/2015​

[/td][td][/td][td]carl[/td][/tr]


[tr][td]

2​

[/td][td]Carl[/td][td]

$4 ​

[/td][td]

$0 ​

[/td][td]

$11 ​

[/td][td][/td][td]

2014​

[/td][/tr]


[tr][td]

3​

[/td][td]Jesse[/td][td]

$3 ​

[/td][td]

$0 ​

[/td][td]

$14 ​

[/td][td][/td][td]

20​

[/td][/tr]


[tr][td]

4​

[/td][td]Jake[/td][td]

$0 ​

[/td][td]

$4 ​

[/td][td]

$0 ​

[/td][td][/td][td][/td][/tr]


[tr][td]

5​

[/td][td]Carl[/td][td]

$0 ​

[/td][td]

$16 ​

[/td][td]

$14 ​

[/td][td][/td][td][/td][/tr]
[/table]

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>