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

subtotal based on two conditions
 

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.

subtotal based on two conditions
 

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" wrote in message
oups.com...
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

subtotal based on two conditions
 

Hi Bob,

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

Richard

--
RMC,CPA


"Bob Phillips" wrote in message ...
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" wrote in message
oups.com...
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

subtotal based on two conditions
 

In article ,
"R. Choate" wrote:

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

See

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

subtotal based on two conditions
 

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" wrote in message
...
Hi Bob,

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

Richard

--
RMC,CPA


"Bob Phillips" wrote in message
...
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" wrote in message
oups.com...
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