Multiple selection criteria using named ranges - can't get it

Darren,

I don't see why you'd need VBA to create a named dynamic range. I propose 2 ways that you could use:

(1) I reckon AllTransactionDates and AllTranactionAmounts are the names that you want to assign to continually/regularly changing ranges.

Activate the first cell of the range for AllTransactionDates. Lets say this cell is A2 on sheet X.
Activate Insert|Name|Define.
Enter AllTransctionDates for Names in Workbook.
Enter the following formula for Refers To:

=OFFSET(x!$A$2,0,0,COUNTA($A:$A),1)

Follow the above proc for the range that will get the name AllTransactionAmounts.

Your array formula will work. Why don't you place your date condition in say A2 and use

{=SUM((AllTransactionDates>A1)*(AllTransactionDates<=A3)*AllTransactionAmounts)}

By the way, you might want to use the non-array formula instead:

=SUMPRODUCT((AllTransactionDates>1/1/2001)*(AllTransactionDates<=A3)*AllTransactionAmounts)}

(2) If the formula is, this is of utmost importance, on the same sheet as the ranges it process, you can use a UDF (see below) instead of named dynamic ranges with the same functionality (keeping track of changes in the relevant ranges). If so or you can meet the precondition just mentioned, add the following UDF to your workbook (via Insert|Module):

Function Used(r As Range) As Range
'
' Harlan Grove
'
Dim q As Range
Set q = r.Parent.UsedRange.Cells(r.Parent.UsedRange.Cells.Count)
Set Used = Intersect(r, r.Parent.Range(Cells(1, 1), q))
End Function

Now, you can re-write the formula, where I assume, in order to illustrate the usage, dates in column C and amounts in D on sheet X.

The SUMPRODUCT (or the array formula) must be re-written as follows and MUST BE ENTERED somewhere in shhet X:

=SUMPRODUCT((USED(C:C)>DateCrit1)*(USED(C:C)<=DateCrit2)*(USED(D:D))

where DateCrit1 and DateCrit2 are lower and upper date points that you want to use as criteria. These criterion dates can be placed anywhere in your workbook.

Aladin

Unexpected result - Value too high.

Aladin I have used your method as recommended to create the named range using the formula supplied

=OFFSET('All Transactions'!$A$2,0,0,COUNTA('All Transactions'!$A:$A),1)

This is what I have in for AllTransactionDates, I found that not having the ' symbol around the sheet name resulted in #Name when using the formaula, again I used the sum product formula you recommended

=SUMPRODUCT((AllTransactionDates>1/1/2001)*(AllTransactionDates<=A3)*AllTransactionAmounts)

I am currently only using the formuala on one cell when I transfer to other cells I will remove the fixed date and use a cell reference. One doesn't exist for starting range at the moment. Subsequent cells will.

Using the above information results in a value far in excess of what it should be. This also occurs when the array formuala is used. It must be something to do with the named range?

Results 556160
Expected 2749.68

Have I done something wrong?

Thanks

Darren.

Re: Unexpected result - Value too high.

That is to be expected with a space in the sheet name. If you have a space, you have to put the name between single quotes. I prefer not to use spaces in sheet names, even in column headings.

when using the formaula, again I used the sum product formula you recommended

It would suprise me if they didn't produce the same result.

It must be something to do with the named range?

No, I don't believe so.

Hard to say. And I'm clueless. If you want me to look at your model, you have my e-mail address.

Aladin

Re: Unexpected result - Value too high.

Aladin,

Thanks for help, I found that when I pasted the formula you suggested into my named range for TransactionAmounts I did not change the starting cell from A2 and column A:A, after changing it, everything worked.

That is great. (NT)