M
Melissa
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!
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!