On 2002-03-17 06:21, daveray wrote:
I use a 2 sheet workbook. The first sheet has a list of sales figures associated with salesmen. On the 2nd sheet I have a summary of the sales - total, average order value etc. I use a simple formula
=SUMIF('Orders Received'F1:F300,"=Accepted",'Orders Received'!C1:C300)
That works fine on a regional basis but on a national basis the sales could very in number form 1000 to 20,000. How can I alter the formula so that it looks at the whole of columns C and F?
All help gratefully received.
Dave
Dave,
There are 2 methods you can use to cope with the ever changing data area.
[1] Compute dynamically the relevant ranges in the summary sheet from within the SUMIF formulas that you use, or
[2] Name the dynamically computed relevant ranges via Insert|Name|Define.
Method 2 makes the named ranges in all worksheets of your WB, while Method 1 keeps them local to the summary sheet.
Lets take Method 1 first.
In say A1 in the summary sheet enter:
=MATCH(9.99999999999999E+307,'Orders Received'!C:C)
Note 1. MATCH is fast enough and looks just at one column, so you don't have to worry about that.
Now you can (re)write your example SUMIF formula as:
=SUMIF(OFFSET('Orders Received'F1,0,0,$A$1,1),"Accepted",OFFSET('Orders Received'!C1,0,0,$A$1,1))
A corresponding AVERAGE formula would be:
=SUMIF(OFFSET('Orders Received'F1,0,0,$A$1,1),"Accepted",OFFSET('Orders Received'!C1,0,0,$A$1,1))/MAX(1,COUNTIF(OFFSET('Orders Received'F1,0,0,$A$1,1),"Accepted"))
Method 2.
Activate the option Insert|Name|Define.
Enter
EndRow as name in the Names in Workbook box.
Enter as formula in the Refers to box:
=MATCH(9.99999999999999E+307,'Orders Received'!C:C)
Click Add.
Enter as name, e.g.,
Orders in the Names in Workbook box.
Enter as formula in the Refers to box:
=OFFSET('Orders Received'$F$1,0,0,EndRow,1)
Note that this defines a dynamic range as one-column range (that is, a dynamic range in F of Orders Received).
Click Add.
Enter as name, e.g.,
Amounts in the Names in Workbook box.
Enter as formula in the Refers to box:
=OFFSET('Orders Received'$C$1,0,0,EndRow,1)
Click OK.
Note 2. You need for every relevant subarea of data area to set up a name and a formula.
After having created named dynamic ranges, you can (re)write your example SUMIF formula as:
=SUMIF(Orders,"Accepted",Amounts)
A corresponding AVERAGE formula would be:
=SUMIF(Orders,"Accepted",Amounts))/MAX(1,COUNTIF(Orders,"Accepted"))
Aladin
This message was edited by Aladin Akyurek on 2002-03-17 08:47 
