Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to subtotal based on an if | Excel Worksheet Functions | |||
Add cells from a range based on 2 conditions from 2 other ranges | Excel Worksheet Functions | |||
SUM based on multiple conditions - SORRY, URGENT!!! | Excel Worksheet Functions | |||
Create a total based on multiple conditions is not giving correct. | Excel Worksheet Functions | |||
SUMIF based on 2 conditions | Excel Worksheet Functions |