Referencing a complete column

[daveray]

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

[Chris Davison]

Dave,

C:C and F:F should do it.

However, this will look at all 65,000 cells in both cases and may slow down the calc

Have a look at dynamic ranges : Dave has some good examples at Ozgrid

http://www.ozgrid.com/Excel/DynamicRanges.htm




_________________
Hope this helps,
Chris


[ This Message was edited by: Chris Davison on 2002-03-17 06:44 ]

[Aladin Akyurek]

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 ]