Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum a range meeting conditions of row & column
I have a table where 1st column has the store names and the 1st row has items
(for some reason they are not unique). For example: Sales RetailExpense StockLoss RetailExpense GrossMargin Dahua 120 -5 -20 -12 20 Hebin 100 -15 -10 -12 16 HuZhou 130 -19 -10 -20 16 Wuxi 100 -29 -15 -17 18 Jinhua 160 -19 -10 -20 16 Then I have to create summaries by stores. I need a formula in each by-store sheet to sum the numbers that meet both the row and the column conditions. eg. a particular store (it's changing sheet by sheet but always has the name in cell J1)'s total retail expenses. I hope this is clear. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum a range meeting conditions of row & column
Either of these solutions should work for you:
http://www.contextures.com/xlFunctio...tml#SumProduct http://peltiertech.com/Excel/Pivots/pivottables.htm Regards, Ryan--- -- RyGuy "jwang036" wrote: I have a table where 1st column has the store names and the 1st row has items (for some reason they are not unique). For example: Sales RetailExpense StockLoss RetailExpense GrossMargin Dahua 120 -5 -20 -12 20 Hebin 100 -15 -10 -12 16 HuZhou 130 -19 -10 -20 16 Wuxi 100 -29 -15 -17 18 Jinhua 160 -19 -10 -20 16 Then I have to create summaries by stores. I need a formula in each by-store sheet to sum the numbers that meet both the row and the column conditions. eg. a particular store (it's changing sheet by sheet but always has the name in cell J1)'s total retail expenses. I hope this is clear. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum a range meeting conditions of row & column
I'm not sure about the 1st link. The solutions don't seem work for a table
with criteria in both column and row headings (especially the matching is not 1 to 1). Pivot-table does work for that kind of problem, but in my case it's just not convenient. The table below is a huge input sheet. Besides input, the file contains a dozens of sheets for each store. Part of each store sheet contains links to display store expenses. The only variable in each sheet is the sheet name (store name). In other words, if I change the sheet name from A to B, the summary will change accordingly. If I use pivot-table, for dozens of stores displaying details at the same time I may have to set up a dozens of pivot tables with each store name. I just think there must be a formula using sum, match, index, offset etc together can solve the problem. €œryguy7272€ç¼–写: Either of these solutions should work for you: http://www.contextures.com/xlFunctio...tml#SumProduct http://peltiertech.com/Excel/Pivots/pivottables.htm Regards, Ryan--- -- RyGuy "jwang036" wrote: I have a table where 1st column has the store names and the 1st row has items (for some reason they are not unique). For example: Sales RetailExpense StockLoss RetailExpense GrossMargin Dahua 120 -5 -20 -12 20 Hebin 100 -15 -10 -12 16 HuZhou 130 -19 -10 -20 16 Wuxi 100 -29 -15 -17 18 Jinhua 160 -19 -10 -20 16 Then I have to create summaries by stores. I need a formula in each by-store sheet to sum the numbers that meet both the row and the column conditions. eg. a particular store (it's changing sheet by sheet but always has the name in cell J1)'s total retail expenses. I hope this is clear. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum a range if two conditions are met | Excel Discussion (Misc queries) | |||
Sum within a date range meeting one other criteria | Excel Worksheet Functions | |||
using sumproduct on subtotals meeting certain conditions | Excel Worksheet Functions | |||
Average of numbers within a range meeting certain criteria | Excel Worksheet Functions | |||
Meeting two conditions before summing | Excel Worksheet Functions |