Using offsets to determine a range

[rickrawfl44]

In my spread I thought I'd found a way to sumif a moving range. However, as a check I tried one set of data with different criteria and the #'s were off. The formula I'm using:

=SUMIF(H$4:H$55539,OFFSET('MassHub Fwds'!C$4,MATCH($K4,'MassHub Fwds'!C$5:C$70,0),1),OFFSET(G$3,MATCH(K4,A$4:A$55539,0),0):OFFSET(G$3,MATCH(DATE(YEAR(K4),MONTH(K4)+1,DAY(K4)),A$4:A$55539,0)-1,0))

The criterion for the SumIf (first offset) are basically prices on another sheet which seems to work fine, I've tested this without the sumif and does what it's told. However, when I try to define the [sum range] with offsets, the #'s are thrown off.

What the 'match' in the sum range offsets are looking at are dates. To paint the picture:

Date; Hour; SumRange; Range; Criteria; Output
9/1/06; 1; 111.50; 61.65; 9/1/06; ...
9/1/06; 2; 90.65; 60.22;
9/1/06; 3; 101; 61.65;
.
.
.
10/1/06; 1; 60.35; 61.65



Basically what's happening is the 'Range' is full of values from another spread (this is what the first offset looks up). The sum range offsets look up the date (the 'Criteria' for my output) for which to sum. One underlying problem I feel is that the 'Range' has multiple values which are the same that occur on different dates; so a SumIf without a 'begin date' and 'end date' will sum up values which are not within the criteria.

Any ideas?

[DonkeyOte]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

On an aside OFFSET is Volatile, it may be you can avoid this by means of INDEX but I think we'd like to see a file first as your formula is quite hard to follow without data to back it up.

[rickrawfl44]

Attached is the dummy sheet. Columns J and K are the output columns I'm looking for. However, another hurdle I'd come across was the fact that as I go further in time, some of the end outputs, in this case 11/1/06, become '0'. I'm stumped as to what could cause that.

Edit: Forgot to mention that what I was having a problem with; the sumif not capturing all of the criteria. Begins occurring after 9/1/06, another problem I cannot explain.

[DonkeyOte]

Your issues are down to the fact that your criteria and summation ranges are of differing proportions.

To keep things simple, to avoid Volatile OFFSET (and very long INDEX) I would advise use of Concatenation

H2: =$A2&":"&$F2
copied down

the above means you can revert to a very efficient and simple SUMIF

J2: =SUMIF($H:$H,$I2&":"&INDEX($P$2:$Q$50,MATCH($I2,$O$2:$O$50),COLUMNS($J2:J2)),$F:$F)
copied across matrix (J2:K4 etc...)

[rickrawfl44]

The formula given retrieves a zero value. The concatenation of the two ranges gives me strings like:

38961:111.339

I don't see how the proportion of my criteria needs to be = to the proportion of my sum ranges. the criteria is simply a lookup for the 'Range'. If the range does not have the lookup in that cell it should not incorporate the adjacent value in the sum range.

[DonkeyOte]

I don't see how the proportion of my criteria needs to be = to the proportion of my sum ranges. the criteria is simply a lookup for the 'Range'. If the range does not have the lookup in that cell it should not incorporate the adjacent value in the sum range.

Then test it... create a new sheet

A1:A5: 1,1,1,2,1
B1:B5: 1,2,3,4,5

Then: =SUMIF(A1:A5,1,B4:B5)

What's the answer ? Is that what you expected ?

The formula given retrieves a zero value.

The formulae provided when entered into your sample file give the desired results.

The concatenation of the two ranges gives me strings like:

38961:111.339

Correct. Dates are Integers. 38961 = 1st Sep 2006 (1900 date system)