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

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

etc.

--

HTH

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


"Sige" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >