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. |
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. |
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. |
All times are GMT +1. The time now is 02:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com