I have a sheet for each month of the year and one of the columns are the dates and just single numbers (1 through 31 obviously) and next to the date column is a price column for our bids corresponding to when that job bid. I want a formula that can look at the date and determine if it was the first week (numbers 1 through 7) and if it matches add up the price corresponding to that job (same row different column). Right now i did that by IF($E$7<=7,$F$7,0)+IF($E$8<=7,$F$8,0)+IF($E$9<=7,$F$9,0)+.... (<=7 because 7 is the end of week 1) and so on but this is tedious and hard to adjust as sometimes there are 30+ jobs in a month. For the second week i have the same thing but instead of IF($E$7<=7,$F$7,0) it's IF($E$7<=14,$F$7,0)+IF($E$8<=14,$F$8,0)+...(<=14 because 14 is the end of week 2) and then at the end i just subtract the first cell so that it takes out anything that was in week 1. Can someone shorten this somehow?
Bookmarks