Thread: SumIf but using a range as a criterion.

I have a large spreadsheet, to which I add 4 weeks worth of data, from a database printout, (cut n paste) to the bottom, this could be any number of rows.

However there is a column with week numbers in for each row.

I can happily use =SUMIF('FORM R60'!$H$1:$H$1248,$A1,'FORM R60'!$AH$1:$AH$1248)

This gives me the info I need on a TOTALS sheet, across various columns and rows, where the formula sits, adjusted for whichever column of data I require totals. This info is then read by another spreadsheet.

However, rather than manually change the formula (to suit a range) to add certain numbers of weeks, as I do now, I would like to include in the formula a condition say where the week numbers are >5 and <10, preferably if the condition could refer to cells.

Say for instance I have Starting Week # and I can input 5 in the cell and then adjacent I can have Ending Week # and I can input 10 in the cell. The following month I can have it start from 11 to 16, and so on.

So the SumIf formula would need to refer to 'FORM R60'!$F$1:$F$5000 > [the cell on the 'TOTALS' sheet which has the commencing week #] to < [the cell on the 'TOTALS' sheet which has the ending week #]. Column F being the one which holds the week number. I anticipate that a year of data will go to 5000 rows.

This would then change every formula in the 'TOTALS' sheet to suit the week number range I enter in the two cells.

I use Excel 2003.

Can it be done?

Thanks

Maybe:

Code:

=Sumproduct(--('FORM R60'!$H$1:$H$5000=$A1),--('FORM R60'!$F$1:$F$5000>'TOTALS'!$X$1),--('FORM R60'!$F$1:$F$5000<'TOTALS'!$Y$1),'FORM R60'!$AH$1:$AH$5000)

Where X1 and Y1 on TOTALS sheet hold the start/end values... change as necessary.

This appears to suit my needs, but is returning a zero!

On the TOTALS there are references to R33 sheet (this is a forcasting tool) and looks forward from week 10 to week 53 (currently).
The R60 is as Invoiced for for the current Period 03 (we have 13 per year) I have a total of weeks 01 to 09.

On another sheet I take the 3 periods cost (displayed by period) and add the R33 cost to give me the total cost for each component. Rail; Sleepers; Ballast & Haulage.

Period 04 will reflect the R33 from week 14 to week 53 and the R60s will be week 01 to 13.

Each successive period reducing the R33 by 4 and increasing the R60 by 4.

I have included comments adjacent to each CELL.

The R33 changes as the planners review each job.

I hope the upload works!!!

Your assistance is greatly appreciated.

Regards Philip

Try this as i just add = sign for start & end week .....

=SUMPRODUCT(--('FORM R60'!$H$2:$H$5000=$A207),--('FORM R60'!$F$2:$F$5000>=TOTALS!$M$197),--('FORM R60'!$F$2:$F$5000<=TOTALS!$N$197),'FORM R60'!$AV$2:$AV$5000)