Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 18
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 18
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
sum a range if two conditions are met Valerie Excel Discussion (Misc queries) 3 August 14th 07 03:48 PM
Sum within a date range meeting one other criteria [email protected] Excel Worksheet Functions 3 January 28th 07 05:53 AM
using sumproduct on subtotals meeting certain conditions Jeff Manning Excel Worksheet Functions 0 November 10th 05 01:35 AM
Average of numbers within a range meeting certain criteria opal23k Excel Worksheet Functions 4 August 25th 05 08:51 PM
Meeting two conditions before summing Andeb Excel Worksheet Functions 1 June 24th 05 10:33 AM


All times are GMT +1. The time now is 03:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"