subtotal based on two conditions

Hello All, i have a .xls file with Sheet 1 as
Date Name qty
1 x 10
1 x 50
1 x 30
1 y 40
1 y 60
1 z 80
2 x 25
2 x 25
2 y 30
2 y 45
2 y 50
3 z 60
4 x 52
4 y 6
4 y 80

In Sheet 2 I have formatting like this
Date 1 2
3 4 (so on)
x <total of x for date 1> <total of x for date 1>
y
z

Is there any formula or way to get subtotals for x,yand z for each
date.
example
Date 1 2
3 4 (so on)
x 90
y 100
z 80

Please help

Sorry ! I forgot to take care of wordwrapping. Alsp, please read <total
of x for date 1> <total of x for date 1> as <total of x for date 1>
<total of x for date 2>.

On Sheet 2 enter this in B2

=SUMPRODUCT(--(Sheet1!$A$2:$A$20=B$1),--(Sheet1!$B$2:$B$20=$A2),Sheet1!$C$2:
$C$20)

copy down and across.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ashish128" <[email protected]> wrote in message
news:[email protected]...
> Hello All, i have a .xls file with Sheet 1 as
> Date Name qty
> 1 x 10
> 1 x 50
> 1 x 30
> 1 y 40
> 1 y 60
> 1 z 80
> 2 x 25
> 2 x 25
> 2 y 30
> 2 y 45
> 2 y 50
> 3 z 60
> 4 x 52
> 4 y 6
> 4 y 80
>
> In Sheet 2 I have formatting like this
> Date 1 2
> 3 4 (so on)
> x <total of x for date 1> <total of x for date 1>
> y
> z
>
> Is there any formula or way to get subtotals for x,yand z for each
> date.
> example
> Date 1 2
> 3 4 (so on)
> x 90
> y 100
> z 80
>
> Please help
>

Hi Bob,

Just curious, why the double minus signs in the formula?

Richard

--
RMC,CPA


"Bob Phillips" <[email protected]> wrote in message news:[email protected]...
On Sheet 2 enter this in B2

=SUMPRODUCT(--(Sheet1!$A$2:$A$20=B$1),--(Sheet1!$B$2:$B$20=$A2),Sheet1!$C$2:
$C$20)

copy down and across.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ashish128" <[email protected]> wrote in message
news:[email protected]...
> Hello All, i have a .xls file with Sheet 1 as
> Date Name qty
> 1 x 10
> 1 x 50
> 1 x 30
> 1 y 40
> 1 y 60
> 1 z 80
> 2 x 25
> 2 x 25
> 2 y 30
> 2 y 45
> 2 y 50
> 3 z 60
> 4 x 52
> 4 y 6
> 4 y 80
>
> In Sheet 2 I have formatting like this
> Date 1 2
> 3 4 (so on)
> x <total of x for date 1> <total of x for date 1>
> y
> z
>
> Is there any formula or way to get subtotals for x,yand z for each
> date.
> example
> Date 1 2
> 3 4 (so on)
> x 90
> y 100
> z 80
>
> Please help
>

In article <[email protected]>,
"R. Choate" <[email protected]> wrote:

> Just curious, why the double minus signs in the formula?

See

http://www.mcgimpsey.com/excel/doubleneg.html

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"R. Choate" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob,
>
> Just curious, why the double minus signs in the formula?
>
> Richard
>
> --
> RMC,CPA
>
>
> "Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> On Sheet 2 enter this in B2
>
>
=SUMPRODUCT(--(Sheet1!$A$2:$A$20=B$1),--(Sheet1!$B$2:$B$20=$A2),Sheet1!$C$2:
> $C$20)
>
> copy down and across.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "ashish128" <[email protected]> wrote in message
> news:[email protected]...
> > Hello All, i have a .xls file with Sheet 1 as
> > Date Name qty
> > 1 x 10
> > 1 x 50
> > 1 x 30
> > 1 y 40
> > 1 y 60
> > 1 z 80
> > 2 x 25
> > 2 x 25
> > 2 y 30
> > 2 y 45
> > 2 y 50
> > 3 z 60
> > 4 x 52
> > 4 y 6
> > 4 y 80
> >
> > In Sheet 2 I have formatting like this
> > Date 1 2
> > 3 4 (so on)
> > x <total of x for date 1> <total of x for date 1>
> > y
> > z
> >
> > Is there any formula or way to get subtotals for x,yand z for each
> > date.
> > example
> > Date 1 2
> > 3 4 (so on)
> > x 90
> > y 100
> > z 80
> >
> > Please help
> >
>
>
>