SUMPRODUCT question

[Guest]

Hi there!
I have my raw data in the following format:
A B C D
Year Mth Country Value
2005 1 Australia 10
2005 1 China 20
2005 2 Australia 10
2005 2 Japan 30
2006 1 Australia 10
2006 2 Australia 10
2006 3 Australia 10

What I would like to sum up are all values for Australia for 2005, month 1
and 2006, months 2-3. From the table above, the answer would be = 30.
The only way I know how to do this is using sumproduct in the following way:
=sumproduct(--(A1:A7="2005"),--(B1:B7="1"),--(C1:C7="Australia"),--(D17))+sumproduct(--(A1:A7="2006"),--(B1:B7="2"),--(C1:C7="Australia"),--(D17))+sumproduct(--(A1:A7="2006"),--(B1:B7="3"),--(C1:C7="Australia"),--(D17))

So you can see I have 3 "parts" in the formula. For the year 2006, can I
specify a range of months (i.e. 2 and 3) instead of having to indicate each
month separately? so something like: --(B1:B7="range from 2 to 3").
Thanks!

 

[Biff]

Hi!

Try this:

=SUMPRODUCT((A1:A7=2005)*(B1:B7=1)*(C1:C7="Australia")*D17)+SUMPRODUCT((A1:A7=2006)*(B1:B7={2,3})*(C1:C7="Australia")*D17)

Biff

 

[Guest]

Thanks for the instantaneous and extremely helpful response! :-D

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Thanks for the instantaneous and extremely helpful response! :-D

 

[Bob Phillips]

An alternative

=SUMPRODUCT((((A1:A8=2005)*(B1:B8=1))+((A1:A8=2006)*(ISNUMBER(MATCH(B1:B8,{2
,3},0)))))*(C1:C8="Australia"),D18)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)