lizo

Hi. I am trying to get this formula to work =SUMIF(Sheet2!J3:O3,"<="&R2,Sheet2!J4:O4... The problem I am running into is that R2 is a drop down list of the days of the week and instead of cumulating them in the order they are listed they are cumulating them based alphabetically. Is there a way to list them in the order that I want. The order is Fri, W/E, Mon, Tue, Wed, Thur. This is also the names and order for the range for the formula and they match the names and order in the drop down list.

Someone mentioned the following option but if I need to copy and past it down it adds the rows above it as well I have to do this in several places on a spreadsheet. Can any one help me??!!

i think you could use the indirect() formula or the offset() formula here

the indirect() formula
copy&paste this in place of the sumif()

=SUM( INDIRECT("sheet2!J4:"& CHAR(73+MATCH( R2,Sheet2!J3:O3,0))&4))

- it sums the row starting at J4 (thats hardcoded in, because based on the formula having <= ...it looks like you will always want to start on the 1st day)
- char(73) is the letter i and adds the column found in the match()
- match() finds say Tue is in M3 so its the 4th column
- so char(73+4) =char(77) or the letter M
- the indirect concatenates the string together
- the sum sums it up

J3 can start with any day
your drop down list can be in any Order
and it should still calculate properly.
since i hardcoded the J4 and the char(73), the formula isnt easily movable. but it works in this case, and should give you an idea of where to start.
hope that helps.