Remember Me?

#1
November 24th 05, 02:10 PM posted to microsoft.public.excel.worksheet.functions
 Sige Posts: n/a
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
November 24th 05, 02:27 PM posted to microsoft.public.excel.worksheet.functions
 Bob Phillips Posts: n/a
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
November 24th 05, 02:43 PM posted to microsoft.public.excel.worksheet.functions
 Ron Coderre Posts: n/a
SumProduct Solution?

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

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
November 24th 05, 03:06 PM posted to microsoft.public.excel.worksheet.functions
 Sige Posts: n/a
SumProduct Solution?

Hi Ron,

It is exactly what it needed...!
Thx

Bob,

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
November 24th 05, 05:52 PM posted to microsoft.public.excel.worksheet.functions
 Bob Phillips Posts: n/a
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,

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
November 25th 05, 04:20 PM posted to microsoft.public.excel.worksheet.functions
 Sige Posts: n/a
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,

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Peter B Excel Worksheet Functions 1 March 7th 05 01:59 PM mark4006 Excel Discussion (Misc queries) 2 March 6th 05 04:40 PM dave Excel Worksheet Functions 1 March 3rd 05 09:31 AM Scott Summerlin Excel Worksheet Functions 12 December 4th 04 05:15 AM ski2004_2005 Excel Worksheet Functions 1 November 12th 04 09:08 PM

All times are GMT +1. The time now is 11:16 AM.