subtotal based on two conditions

  • Thread starter Thread starter ashish128
  • Start date Start date
A

ashish128

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)
 
Hi Bob,

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

Richard

--
RMC,CPA


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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sum rows if muliple cells meet criteria but not others 4
Ungrouping the fields inside pivot table(Excel 2007) 1
2 level subtotal result weird 2
Scrabble Value calculation for Welsh words 0
% of Subtotal in PivotTable 2
Sort, select and average macro help 2
Summary count from different worksheets? 4
Transform calendarized data 4

Back
Top