Formula for summary from two column

[dave]

Date Fruits Quantity Fruits 2007 2008
2007 Apple 10 Apple
2007 Orange 56 mango
2007 Mango 48 watermelon
2007 Pineapple 87 orange
2007 Mango 125 strawbery
2007 Citrus 45 citrus
2007 Banana 987 banana
2007 Pineapple 265 grape
2008 Citrus 485 papaya
2008 Banana 698 pineapple
2008 Grape 463
2008 Papaya 216
2008 strawbery 254
2008 Banana 744
2007 Orange 885
2007 Apple 1236
2007 Citrus 1214
2008 watermelon 1546
2008 Apple 8952
2008 Papaya 874
2008 Banana 236
2008 Pineapple 95
2008 Orange 4123
2008 Citrus 236
2007 Banana 216
2008 Mango 956
2008 watermelon 211
2007 watermelon 117
2007 strawbery 261
2007 Apple 112
2008 Grape 234
2008 Grape 278
2007 Banana 678
2008 Apple 330

Hello,

Above is the scenario. How to sum the each type of fruits according
the the year. How to calculate the sum of apple for 2007 & 2008. this
must apply for each type of fruits. Any formula?

 

[Paul C]

=sumproduct(--($A$1:$a$40=e$1),--($B$1:$B$40=$d2,$c$1:$c$40)

Assuming the 2007 is in E1 and Apple is D2

 

[Paul C]

Oops for got a )

=sumproduct(--($A$1:$a$40=e$1),--($B$1:$B$40=$d2),$c$1:$c$40)

 

[Per Jessen]

Hi Dave

You should use a Pivot table to solve this.

See Debra Dalgleish site for an introduciton:

http://www.contextures.com/Pubn01.html

Regards,
Per

 

[Domenic]

Assuming that A2:C35 contains the source data, E2:E11 contains a unique
list of fruits, F1 contains 2007, and G1 contains 2008, try...

F2, copied down and over to Column G:

=SUMPRODUCT(--($A$2:$A$35=F$1),--($B$2:$B$35=$E2),$C$2:$C$35)