sum a range meeting conditions of row & column

[jwang036]

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.

 

[ryguy7272]

Either of these solutions should work for you:
http://www.contextures.com/xlFunctions01.html#SumProduct

http://peltiertech.com/Excel/Pivots/pivottables.htm


Regards,
Ryan---

 

[jwang036]

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â€ç¼–写：