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"),--(D1:D7))+sumproduct(--(A1:A7="2006"),--(B1:B7="2"),--(C1:C7="Australia"),--(D1:D7))+sumproduct(--(A1:A7="2006"),--(B1:B7="3"),--(C1:C7="Australia"),--(D1:D7))

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!

Hi!

Try this:

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

Biff

"Melissa" wrote in message
...
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"),--(D1:D7))+sumproduct(--(A1:A7="2006"),--(B1:B7="2"),--(C1:C7="Australia"),--(D1:D7))+sumproduct(--(A1:A7="2006"),--(B1:B7="3"),--(C1:C7="Australia"),--(D1:D7))

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!

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

"Biff" wrote:

Hi!

Try this:

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

Biff

"Melissa" wrote in message
...
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"),--(D1:D7))+sumproduct(--(A1:A7="2006"),--(B1:B7="2"),--(C1:C7="Australia"),--(D1:D7))+sumproduct(--(A1:A7="2006"),--(B1:B7="3"),--(C1:C7="Australia"),--(D1:D7))

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!

You're welcome. Thanks for the feedback!

Biff

"Melissa" wrote in message
...
Thanks for the instantaneous and extremely helpful response! :-D

"Biff" wrote:

Hi!

Try this:

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

Biff

"Melissa" wrote in message
...
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"),--(D1:D7))+sumproduct(--(A1:A7="2006"),--(B1:B7="2"),--(C1:C7="Australia"),--(D1:D7))+sumproduct(--(A1:A7="2006"),--(B1:B7="3"),--(C1:C7="Australia"),--(D1:D7))

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!

An alternative

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

--
HTH

Bob Phillips

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

"Melissa" wrote in message
...
Thanks for the instantaneous and extremely helpful response! :-D

"Biff" wrote:

Hi!

Try this:


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

Biff

"Melissa" wrote in message
...
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"),--(D1:D7))+
sumproduct(--(A1:A7="2006"),--(B1:B7="2"),--(C1:C7="Australia"),--(D1:D7))+s
umproduct(--(A1:A7="2006"),--(B1:B7="3"),--(C1:C7="Australia"),--(D1:D7))

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!