# Perfom calculation based on date in range

1. ## Perfom calculation based on date in range

I have been looking at this for hours and I'm sure there is an answer but I can't think of it right now.
Various Index/Match tests are not giving me the answer I seek.

I have a range of dates (the sunday of each week) across some columns (K->AC).
To the left I have a dozen rows of tasks.
Each task has 4 columns with hours (C->F) and 4 with dates (G->J).

I am trying to make a formula that will look at each date (G->J) for the row, if the date (G->J) is between the current column's date (K->AC) then enter the corresponding summed hours (C->F) in the cell.

I've attached an example file.

Maybe I'm trying to do too much?

2. ## Re: Perfom calculation based on date in range

Put this formula in cell K10:

=SUMIFS(\$C10:\$F10,\$G10:\$J10,">="&K\$8,\$G10:\$J10,"<"&K\$8+7)

then copy across and down as required.

Hope this helps.

Pete

3. ## Re: Perfom calculation based on date in range

Thank you @ Pete_UK! I was over thinking and over complicating it as always.

I made one small change, I added an extra column with a date outside my expected range, and modified K\$8+7 to L\$9, just in case one of the dates ends up being changed to some oddball one that is not a Sunday (maybe due to a holiday or something).

Final formula in cell K10 is:

=SUMIFS(\$C10:\$F10,\$G10:\$J10,">="&K\$8,\$G10:\$J10,"<"&L\$8)

4. ## Re: Perfom calculation based on date in range

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1