Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 24th 05, 02:10 PM posted to microsoft.public.excel.worksheet.functions
Sige
 
Posts: n/a
Default SumProduct Solution?

Hi There,

A B A B B A A B A ....
A4251
A4231
A4261 Data numbers
A4272
A4278
AUnique
....

I would like to sum all data for which criteria in first row = A
&
Code in first column equals eg A4261

Explained differently: summing all data which is on line A4261,
condition that there is an A in the first row.

Any help really appreciated,
Sige


  #2   Report Post  
Old November 24th 05, 02:27 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default SumProduct Solution?

=SUMIF($B$1:$AZ$1,"A",$B2:$AZ2)

etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sige" wrote in message
oups.com...
Hi There,

A B A B B A A B A ....
A4251
A4231
A4261 Data numbers
A4272
A4278
AUnique
...

I would like to sum all data for which criteria in first row = A
&
Code in first column equals eg A4261

Explained differently: summing all data which is on line A4261,
condition that there is an A in the first row.

Any help really appreciated,
Sige



  #3   Report Post  
Old November 24th 05, 02:43 PM posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default SumProduct Solution?

If your data is in Cells A1:G10, try this:
=SUMPRODUCT((A2:A10="A4261")*(B1:G1="A")*B2:G10)

Adjust the range references to suit your situation.

Does that help?

***********
Regards,
Ron


"Sige" wrote:

Hi There,

A B A B B A A B A ....
A4251
A4231
A4261 Data numbers
A4272
A4278
AUnique
....

I would like to sum all data for which criteria in first row = A
&
Code in first column equals eg A4261

Explained differently: summing all data which is on line A4261,
condition that there is an A in the first row.

Any help really appreciated,
Sige


  #4   Report Post  
Old November 24th 05, 03:06 PM posted to microsoft.public.excel.worksheet.functions
Sige
 
Posts: n/a
Default SumProduct Solution?

Hi Ron,

It is exactly what it needed...!
Thx

Bob,

I read your excellent paper about Sumproduct ..and Names. I have a
question about the latter too:
Quote
If the 'Data Validation' cell is on a different worksheet from the list
range, you will get an error if you try to use a range reference, even
if preceded by the worksheet name. The way to overcome this is to
define a Name for that list range, and use that in the 'Data
Validation' list Source editbox.
<Unquote

If I define a Name and would like to use it as a List-range for the
datavalidation ... I get the Defined Name in the list but not the items
defined in my Range
So name "SIGE" = B2:B10
Will show me "SIGE" in my list, instead of the items in the defined
range.

Brgds Sige

  #5   Report Post  
Old November 24th 05, 05:52 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default SumProduct Solution?

Sige,

You have to use = before the name, such as =SIGE, not just the range name.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sige" wrote in message
oups.com...
Hi Ron,

It is exactly what it needed...!
Thx

Bob,

I read your excellent paper about Sumproduct ..and Names. I have a
question about the latter too:
Quote
If the 'Data Validation' cell is on a different worksheet from the list
range, you will get an error if you try to use a range reference, even
if preceded by the worksheet name. The way to overcome this is to
define a Name for that list range, and use that in the 'Data
Validation' list Source editbox.
<Unquote

If I define a Name and would like to use it as a List-range for the
datavalidation ... I get the Defined Name in the list but not the items
defined in my Range
So name "SIGE" = B2:B10
Will show me "SIGE" in my list, instead of the items in the defined
range.

Brgds Sige





  #6   Report Post  
Old November 25th 05, 04:20 PM posted to microsoft.public.excel.worksheet.functions
Sige
 
Posts: n/a
Default SumProduct Solution?

Blushing like a Purbeck...
Thanks Bob


Bob Phillips wrote:
Sige,

You have to use = before the name, such as =SIGE, not just the range name.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sige" wrote in message
oups.com...
Hi Ron,

It is exactly what it needed...!
Thx

Bob,

I read your excellent paper about Sumproduct ..and Names. I have a
question about the latter too:
Quote
If the 'Data Validation' cell is on a different worksheet from the list
range, you will get an error if you try to use a range reference, even
if preceded by the worksheet name. The way to overcome this is to
define a Name for that list range, and use that in the 'Data
Validation' list Source editbox.
<Unquote

If I define a Name and would like to use it as a List-range for the
datavalidation ... I get the Defined Name in the list but not the items
defined in my Range
So name "SIGE" = B2:B10
Will show me "SIGE" in my list, instead of the items in the defined
range.

Brgds Sige




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 Peter B Excel Worksheet Functions 1 March 7th 05 01:59 PM
Generating excel combinations mark4006 Excel Discussion (Misc queries) 2 March 6th 05 04:40 PM
sumproduct causing memory errors? dave Excel Worksheet Functions 1 March 3rd 05 09:31 AM
Sumproduct function not working Scott Summerlin Excel Worksheet Functions 12 December 4th 04 05:15 AM
adding two sumproduct formulas together ski2004_2005 Excel Worksheet Functions 1 November 12th 04 09:08 PM


All times are GMT +1. The time now is 01:21 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017