Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL.SUM OF DIFFERENT COLUMS AND LINES ON DIFFERENT SHEET REPEAT
if sheet one colum c has tobacco,gas,food, ect. and colum d has totals of day
and there repeated lines like this down the colum. how do I get the sum of all lines that say tobacco, or gas, or food, ect to auto compute on another sheet? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL.SUM OF DIFFERENT COLUMS AND LINES ON DIFFERENT SHEET REPEAT
Here are 2 ways:
Use a Pivot Table where the layout would be: ROWS: Category DATA: Sum of Totals or a formula like this would do it: =SUMIF(C1:C100,"food",D1:D100) returns the total for "food" lines. Adjust range references to suit your situation. Does that help? *********** Regards, Ron "mark d davis" wrote: if sheet one colum c has tobacco,gas,food, ect. and colum d has totals of day and there repeated lines like this down the colum. how do I get the sum of all lines that say tobacco, or gas, or food, ect to auto compute on another sheet? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clarification
If you want the items on Sheet1 summarized on Sheet2, then on Sheet2 the
formula would be more like this: =SUMIF(Sheet1!C1:C100,"food",Sheet1!D1:D100) returns the total for "food" lines. and if on Sheet2: A1: Food B1: =SUMIF(Sheet1!C1:C100,A1,Sheet1!D1:D100) returns the total of Food items from Sheet1 *********** Regards, Ron "Ron Coderre" wrote: Here are 2 ways: Use a Pivot Table where the layout would be: ROWS: Category DATA: Sum of Totals or a formula like this would do it: =SUMIF(C1:C100,"food",D1:D100) returns the total for "food" lines. Adjust range references to suit your situation. Does that help? *********** Regards, Ron "mark d davis" wrote: if sheet one colum c has tobacco,gas,food, ect. and colum d has totals of day and there repeated lines like this down the colum. how do I get the sum of all lines that say tobacco, or gas, or food, ect to auto compute on another sheet? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clarification
this didnt seem to work. I copied and past the formula. and tried typing it
in. cant seem to get it to work. any other ideas. "Ron Coderre" wrote: If you want the items on Sheet1 summarized on Sheet2, then on Sheet2 the formula would be more like this: =SUMIF(Sheet1!C1:C100,"food",Sheet1!D1:D100) returns the total for "food" lines. and if on Sheet2: A1: Food B1: =SUMIF(Sheet1!C1:C100,A1,Sheet1!D1:D100) returns the total of Food items from Sheet1 *********** Regards, Ron "Ron Coderre" wrote: Here are 2 ways: Use a Pivot Table where the layout would be: ROWS: Category DATA: Sum of Totals or a formula like this would do it: =SUMIF(C1:C100,"food",D1:D100) returns the total for "food" lines. Adjust range references to suit your situation. Does that help? *********** Regards, Ron "mark d davis" wrote: if sheet one colum c has tobacco,gas,food, ect. and colum d has totals of day and there repeated lines like this down the colum. how do I get the sum of all lines that say tobacco, or gas, or food, ect to auto compute on another sheet? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clarification
It's difficult to figure out what's not working without more details.
Can you post an example of the formula you are trying to use along with any other pertinent information: sheet names, ranges, etc? *********** Regards, Ron "mark d davis" wrote: this didnt seem to work. I copied and past the formula. and tried typing it in. cant seem to get it to work. any other ideas. "Ron Coderre" wrote: If you want the items on Sheet1 summarized on Sheet2, then on Sheet2 the formula would be more like this: =SUMIF(Sheet1!C1:C100,"food",Sheet1!D1:D100) returns the total for "food" lines. and if on Sheet2: A1: Food B1: =SUMIF(Sheet1!C1:C100,A1,Sheet1!D1:D100) returns the total of Food items from Sheet1 *********** Regards, Ron "Ron Coderre" wrote: Here are 2 ways: Use a Pivot Table where the layout would be: ROWS: Category DATA: Sum of Totals or a formula like this would do it: =SUMIF(C1:C100,"food",D1:D100) returns the total for "food" lines. Adjust range references to suit your situation. Does that help? *********** Regards, Ron "mark d davis" wrote: if sheet one colum c has tobacco,gas,food, ect. and colum d has totals of day and there repeated lines like this down the colum. how do I get the sum of all lines that say tobacco, or gas, or food, ect to auto compute on another sheet? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clarification
OK, Mark....See if this works for you:
L2: $5 PHONE L3: $10 PHONE L4: TOBACCO L5: GROCERIES L6: DRINKS L7: OIL L8: FOOD L9: ICE L10: GAS L11: PAPERS L12: OFF ROAD L13: DIESEL L14: KERO L15: TOTAL M2: =SUMIF($X$2:$X$111,L2,$Y$2:$Y$111) Copy that formula down through M14 M15: =SUM(M2:M14) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "mark d davis" wrote: sheet one:shift reports completed. there is a lot of information on the sheet. I need to transfer each total so that i can use it for sales tax and other business reasons. I dont need it to total yet. just transfer without copy and past each one of them.. if you have an email i will send you a sample sheet. mine is . thank you mark davis starts close to the top of page and goes forever. two reports per day. this is not all the information on the sheet. Phone card $0.00 Groceries $250.00 Food $354.00 Tobacco $1,257.00 Gas $2,587.00 Diesel $538.00 Off road $879.00 Kerosene $130.00 "Ron Coderre" wrote: It's difficult to figure out what's not working without more details. Can you post an example of the formula you are trying to use along with any other pertinent information: sheet names, ranges, etc? *********** Regards, Ron "mark d davis" wrote: this didnt seem to work. I copied and past the formula. and tried typing it in. cant seem to get it to work. any other ideas. "Ron Coderre" wrote: If you want the items on Sheet1 summarized on Sheet2, then on Sheet2 the formula would be more like this: =SUMIF(Sheet1!C1:C100,"food",Sheet1!D1:D100) returns the total for "food" lines. and if on Sheet2: A1: Food B1: =SUMIF(Sheet1!C1:C100,A1,Sheet1!D1:D100) returns the total of Food items from Sheet1 *********** Regards, Ron "Ron Coderre" wrote: Here are 2 ways: Use a Pivot Table where the layout would be: ROWS: Category DATA: Sum of Totals or a formula like this would do it: =SUMIF(C1:C100,"food",D1:D100) returns the total for "food" lines. Adjust range references to suit your situation. Does that help? *********** Regards, Ron "mark d davis" wrote: if sheet one colum c has tobacco,gas,food, ect. and colum d has totals of day and there repeated lines like this down the colum. how do I get the sum of all lines that say tobacco, or gas, or food, ect to auto compute on another sheet? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make a template that will print all the lines and colums | New Users to Excel | |||
How to create a chart with Stacking colums and lines with 2 axes | Charts and Charting in Excel |