Sum with multiple dates

[igbaria]

Hi people. Thanks again for the help.

I'm facing a new problem with a very large sheet. In fact, I want to sum the column "Extension" when a couple of criterias is true, The example:

Place Extension Date1 Date2 Date3 Date4 ............. Date21
A 3,00 12/01/2005 06/15/2005 11/22/2005 04/23/2006 09/14/2021
A 2,00 12/01/2005 06/15/2005 11/22/2005 04/23/2006 09/14/2021
A 3,50 12/01/2005 06/15/2005 11/22/2005 04/23/2006 09/14/2021
B 4,80 12/01/2005 06/15/2005 11/22/2005 04/23/2006 09/14/2021
B 2,50 12/01/2005 06/15/2005 11/22/2005 04/23/2006 09/14/2021
B 3,30 12/01/2005 06/15/2005 11/22/2005 04/23/2006 09/14/2021
C 5,15 12/01/2005 06/15/2005 11/22/2005 04/23/2006 09/14/2021
C 2,75 12/01/2005 06/15/2005 11/22/2005 04/23/2006 09/14/2021
C 2,25 12/01/2005 06/15/2005 11/22/2005 04/23/2006 09/14/2021
.
.
.
.
Z 3,25 12/01/2005 06/15/2005 11/22/2005 04/23/2006 09/14/2021
Z 2,50 12/01/2005 06/15/2005 11/22/2005 04/23/2006 09/14/2021
Z 4,55 12/01/2005 06/15/2005 11/22/2005 04/23/2006 09/14/2021


And, I want this results:

Place 2005 2006 2007 2008 2009 2010............2021
A Sum Ext Sum Ext Sum Ext Sum Ext......................................Sum Ext
B .............................................................................................................
C .................................................................................................Sum ext
.
.
.
Z Sum Ext Sum Ext ................................................................... Sum Ext


Can any one help me to solve this?

Thanks

 

[Zincere]

=SUM(IF((A1:A10=1)*(B1:B10=2)*(C1:C10=3),D1:D10,0)) 'Suppose D1:D10 is what you want to be summed.

Whereas 1 = 1st Criteria, 2 = 2nd Criteria, 3= 3rd Criteria

And Press Ctrl+Shift+Enter not just Enter.

 

[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]

H​

[/td][td]

I​

[/td][td]

J​

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

1​

[/td][td]Place[/td][td]Extension[/td][td]Date1[/td][td]Date2[/td][td]Date3[/td][td]Place[/td][td]

2005​

[/td][td]

2006​

[/td][/tr]


[tr][td]

2​

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

3​

[/td][td]

12/1/2005​

[/td][td]

6/15/2005​

[/td][td]

11/22/2005​

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

25.5​

[/td][td]

0​

[/td][/tr]


[tr][td]

3​

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

2​

[/td][td]

12/1/2005​

[/td][td]

6/15/2005​

[/td][td]

11/22/2005​

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

28.5​

[/td][td]

0​

[/td][/tr]


[tr][td]

4​

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

3.5​

[/td][td]

12/1/2005​

[/td][td]

6/15/2005​

[/td][td]

11/22/2005​

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


[tr][td]

5​

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

4.8​

[/td][td]

12/1/2005​

[/td][td]

6/15/2005​

[/td][td]

11/22/2005​

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


[tr][td]

6​

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

2.5​

[/td][td]

12/1/2005​

[/td][td]

6/15/2005​

[/td][td]

11/22/2005​

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


[tr][td]

7​

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

3.3​

[/td][td]

12/1/2005​

[/td][td]

6/15/2005​

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

In I2 control+shift+enter, not just enter, copy across, and down:

<strike></strike>=SUM(IF($A$2:$A$7=$H2,IF(YEAR($C$2:$E$7)=I$1,$B$2:$B$7)))

 

[igbaria]

I knew it was simple. Shame on me.......