#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 122
Default SUMPRODUCT question

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!

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,688
Default SUMPRODUCT question

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!



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 122
Default SUMPRODUCT question

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!




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,688
Default SUMPRODUCT question

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!






  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default SUMPRODUCT question

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!








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct Question [email protected] Excel Discussion (Misc queries) 5 May 16th 06 03:43 PM
Sumproduct Question [email protected] Excel Discussion (Misc queries) 9 March 10th 06 04:26 PM
Question about sumproduct Jason Excel Discussion (Misc queries) 1 April 21st 05 05:44 PM
Question about sumproduct bj Excel Discussion (Misc queries) 0 April 21st 05 05:40 PM
sumproduct question taxmom Excel Worksheet Functions 3 April 18th 05 07:01 PM


All times are GMT +1. The time now is 06:04 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"