i have a series of events (900+) that occurred throughout the year. i want to sum them by the week they were in (ie sumif the date the occurred is greater than or equal than the first day of the week AND less than or equal to the last day of the week). the only thing is that i can’t get the and statement to work correctly in the sumif function. i’ve also tried a vlookup to assign a week number to each event (running the sumif on that result) but again i can’t get the embedded and statement to work properly. does anyone have any suggestions? thanks.
If you have each week assigned a number you could use three sumif statements. (This is probably the longer, less elegant solution): Sum everything minus sumif(all weeks before desired week) minus sumif(all weeks after desired week). This avoids embedding an and() in the sumif.
I would make a lookup table that assigns a week # to every day of the year (every 7 observations = a new week #), and then use a vlookup to append that week # to your existing data based on the observation date. A quick pivot table could then sum by week #.
thanks for the help. a collegue suggested something similar to what young_prof said and that worked well. thanks again.