SumProduct Solution?

S

Sige

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
 
B

Bob Phillips

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

etc.

--

HTH

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

Ron Coderre

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
 
S

Sige

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
 
B

Bob Phillips

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)
 
S

Sige

Blushing like a Purbeck...
Thanks Bob


Bob said:
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)
Click to expand...
 
Top