SUMIFS with data to be selected between a range of dates

R

rohan87k

New Member
Joined
Mar 20, 2017
Messages
4
Hi All,
Im currently working on an Excel sheet using the SUMIFS fucntion.

Here is the range:
[TABLE="width: 349"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Range 1[/TD]
[TD]Hours[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]TM 1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1/25/2017[/TD]
[TD="align: right"]1/27/2017[/TD]
[/TR]
[TR]
[TD]TM 1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1/25/2017[/TD]
[TD="align: right"]1/28/2017[/TD]
[/TR]
[TR]
[TD]TM 2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1/29/2017[/TD]
[TD="align: right"]1/30/2017[/TD]
[/TR]
[TR]
[TD]TM 3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1/31/2017[/TD]
[TD="align: right"]2/3/2017[/TD]
[/TR]
[TR]
[TD]TM 2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2/3/2017[/TD]
[TD="align: right"]2/5/2017[/TD]
[/TR]
[TR]
[TD]TM 2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2/5/2017[/TD]
[TD="align: right"]2/8/2017[/TD]
[/TR]
[TR]
[TD]TM 3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2/8/2017[/TD]
[TD="align: right"]2/11/2017[/TD]
[/TR]
[TR]
[TD]TM 3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2/11/2017[/TD]
[TD="align: right"]2/14/2017[/TD]
[/TR]
[TR]
[TD]TM 1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2/14/2017[/TD]
[TD="align: right"]2/16/2017[/TD]
[/TR]
[TR]
[TD]TM 1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2/16/2017[/TD]
[TD="align: right"]2/18/2017[/TD]
[/TR]
</tbody>[/TABLE]

Im using the SUMIFS function as: =SUMIFS(!$C$2:$C$20,$D$2:$D$20,"2/2/2017",!$B$2:$B$20,"TM1")
to calculate hours spent by TM1 on a particular day

The formula works well when the hours are spent on a single day. But when the effort is distributed over a period of days, the formula fails. Any ideas ??
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Hi and welcome to MrExcel,

Take a look at this:


Excel 2016 (Windows) 64 bit
ABCDEFG
1Range 1HoursStart DateEnd Date
2TM 1825-1-201727-1-2017Start25-1-2017
3TM 1725-1-201728-1-2017End8-2-2017
4TM 2629-1-201730-1-2017RangeTM 1
5TM 31031-1-20173-2-2017Hours15
6TM 2103-2-20175-2-2017
7TM 2105-2-20178-2-2017
8TM 3108-2-201711-2-2017
9TM 31011-2-201714-2-2017
10TM 11014-2-201716-2-2017
11TM 11016-2-201718-2-2017
Sheet1
Cell Formulas
RangeFormula
G5=SUMIFS(B2:B11,C2:C11,">="&G2,D2:D11,"<="&G3,A2:A11,G4)
 
Upvote 0
Thanks for the prompt response, but this does not solve my problem.

Im looking to solve for a specific date that falls between a range & not a specified START DATE & END DATE.

For eg: In the mentioned example, i want to know how much hours are spent by TM3 on 1st of Feb.

Expected logic: The hours are divided equally into the number of days between C and D, & the result should only show hours spent by TM3 on 1st of Feb.


------------------------------------------------------------------------------------------


jorismoerings said:
Hi and welcome to MrExcel,

Take a look at this:

Excel 2016 (Windows) 64 bit
ABCDEFG
Range 1 Hours Start Date End Date
TM 1 Start
TM 1 End
TM 2 RangeTM 1
TM 3 Hours
TM 2
TM 2
TM 3
TM 3
TM 1
TM 1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]25-1-2017[/TD]
[TD="align: right"]27-1-2017[/TD]
[TD="align: right"][/TD]

[TD="align: right"]25-1-2017[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]25-1-2017[/TD]
[TD="align: right"]28-1-2017[/TD]
[TD="align: right"][/TD]

[TD="align: right"]8-2-2017[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]29-1-2017[/TD]
[TD="align: right"]30-1-2017[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]31-1-2017[/TD]
[TD="align: right"]3-2-2017[/TD]
[TD="align: right"][/TD]

[TD="align: right"]15[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]3-2-2017[/TD]
[TD="align: right"]5-2-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]5-2-2017[/TD]
[TD="align: right"]8-2-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]8-2-2017[/TD]
[TD="align: right"]11-2-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]11-2-2017[/TD]
[TD="align: right"]14-2-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]14-2-2017[/TD]
[TD="align: right"]16-2-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]16-2-2017[/TD]
[TD="align: right"]18-2-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G5[/TH]
[TD="align: left"]=SUMIFS(B2:B11,C2:C11,">="&G2,D2:D11,"<="&G3,A2:A11,G4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
Hi,

Something like this:


Excel 2016 (Windows) 64 bit
ABCDEFG
1Range 1HoursStart DateEnd Date
2TM 1825-1-201727-1-2017Start1-2-2017
3TM 1725-1-201728-1-2017RangeTM 3
4TM 2629-1-201730-1-2017Hours2,5
5TM 31031-1-20173-2-2017
6TM 2103-2-20175-2-2017
7TM 2105-2-20178-2-2017
8TM 3108-2-201711-2-2017
9TM 31011-2-201714-2-2017
10TM 11014-2-201716-2-2017
11TM 11016-2-201718-2-2017
Sheet1
Cell Formulas
RangeFormula
G4{=IFERROR(SUMIFS(B2:B11,A2:A11,G3,C2:C11,"<="&G2,D2:D11,">="&G2)/(1+MAX(IF((A2:A11=G3)*(C2:C11<=G2)*(D2:D11>=G2),D2:D11,""))-MIN(IF((A2:A11=G3)*(C2:C11<=G2)*(D2:D11>=G2),C2:C11,""))),0)}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
Thanks !!!! This works like a charm... :)

On an advanced note, can we edite this formula to include only workdays ??

jorismoerings said:
Hi,

Something like this:

Excel 2016 (Windows) 64 bit
ABCDEFG
Range 1 Hours Start Date End Date
TM 1Start
TM 1RangeTM 3
TM 2Hours
TM 3
TM 2
TM 2
TM 3
TM 3
TM 1
TM 1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]25-1-2017[/TD]
[TD="align: right"]27-1-2017[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-2-2017[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]25-1-2017[/TD]
[TD="align: right"]28-1-2017[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]29-1-2017[/TD]
[TD="align: right"]30-1-2017[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2,5[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]31-1-2017[/TD]
[TD="align: right"]3-2-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]3-2-2017[/TD]
[TD="align: right"]5-2-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]5-2-2017[/TD]
[TD="align: right"]8-2-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]8-2-2017[/TD]
[TD="align: right"]11-2-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]11-2-2017[/TD]
[TD="align: right"]14-2-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]14-2-2017[/TD]
[TD="align: right"]16-2-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]16-2-2017[/TD]
[TD="align: right"]18-2-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G4[/TH]
[TD="align: left"]{=IFERROR(SUMIFS(B2:B11,A2:A11,G3,C2:C11,"<="&G2,D2:D11,">="&G2)/(1+MAX(IF((A2:A11=G3)*(C2:C11<=G2)*(D2:D11>=G2),D2:D11,""))-MIN(IF((A2:A11=G3)*(C2:C11<=G2)*(D2:D11>=G2),C2:C11,""))),0)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
Sure, keep in mind it's not common practice to go back and forth with additional questions.
Try to ask your questions as completely as possibly can.
Take a close look at forum rules which you agreed to when signing on.

Having said that, take a look at this:


Excel 2016 (Windows) 64 bit
ABCDEFG
1Range 1HoursStart DateEnd Date
2TM 1825-1-201727-1-2017Start26-1-2017
3TM 1725-1-201728-1-2017RangeTM 1
4TM 2629-1-201730-1-2017Hours5
5TM 31031-1-20173-2-2017
6TM 2103-2-20175-2-2017
7TM 2105-2-20178-2-2017
8TM 3108-2-201711-2-2017
9TM 31011-2-201714-2-2017
10TM 11014-2-201716-2-2017
11TM 11016-2-201718-2-2017
Sheet1
Cell Formulas
RangeFormula
G4{=IFERROR(SUMIFS(B2:B11,A2:A11,G3,C2:C11,"<="&G2,D2:D11,">="&G2)/NETWORKDAYS(MIN(IF((A2:A11=G3)*(C2:C11<=G2)*(D2:D11>=G2),C2:C11,"")),MAX(IF((A2:A11=G3)*(C2:C11<=G2)*(D2:D11>=G2),D2:D11,""))),0)}
Press CTRL+SHIFT+ENTER to enter array formulas.


It's now based on workdays only.
 
Upvote 0
Ill keep that in mind.

I also noticed that, this formula fails when there are multiple entries with same start dates but different end dates.

For eg:

Range 1HoursStart DateEnd Date
TM 1Start
TM 1RangeTM 1
TM 2Hours
TM 3
TM 2
TM 2
TM 3
TM 3
TM 1
TM 1

<tbody>
</tbody><thead>
[TH="align: center"]
A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="align: center"]F[/TH]
[TH="align: center"]G[/TH]

</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]25-1-2017[/TD]
[TD="align: right"]25-1-2017[/TD]
[TD="align: right"][/TD]

[TD="align: right"]26-1-2017[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]25-1-2017[/TD]
[TD="align: right"]28-1-2017[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]29-1-2017[/TD]
[TD="align: right"]30-1-2017[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]31-1-2017[/TD]
[TD="align: right"]3-2-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]3-2-2017[/TD]
[TD="align: right"]5-2-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]5-2-2017[/TD]
[TD="align: right"]8-2-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]8-2-2017[/TD]
[TD="align: right"]11-2-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]11-2-2017[/TD]
[TD="align: right"]14-2-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]14-2-2017[/TD]
[TD="align: right"]16-2-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]16-2-2017[/TD]
[TD="align: right"]18-2-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

For eg, in the above case, hours for TM1 for 25th August should be = 8 + (7/4) = 9.75. But the formula returns = (8+7)/4= 3.75.
 
Upvote 0
Hi,

In most cases the devil is in the unasked details. For all i know the formula isn't failing but doesn't comply with your logic.

Eg: your calculation logic is incorrect because the correct answer for the 25th should be (8/1)+(7/3)
cause the 28th (last date second row) is a saturday and hence should be excluded.

Nevertheless take a look at this:


Excel 2016 (Windows) 64 bit
ABCDEFG
1Range 1HoursStart DateEnd Date
2TM 1825-1-201725-1-2017Start25-1-2017
3TM 1725-1-201728-1-2017RangeTM 1
4TM 2629-1-201730-1-2017Hours3,75
5TM 31031-1-20173-2-2017Hours10,33333
6TM 2103-2-20175-2-2017
7TM 2105-2-20178-2-2017
8TM 3108-2-201711-2-2017
9TM 31011-2-201714-2-2017
10TM 11014-2-201716-2-2017
11TM 11016-2-201718-2-2017
Sheet1
Cell Formulas
RangeFormula
G4{=IFERROR(SUMIFS(B2:B11,A2:A11,G3,C2:C11,"<="&G2,D2:D11,">="&G2)/(1+MAX(IF((A2:A11=G3)*(C2:C11<=G2)*(D2:D11>=G2),D2:D11,""))-MIN(IF((A2:A11=G3)*(C2:C11<=G2)*(D2:D11>=G2),C2:C11,""))),0)}
G5{=SUM(IFERROR(INDEX(INDEX(IF((A2:A11=G3)*(C2:C11<=G2)*(D2:D11>=G2),B2:B11,""),0)/INDEX(NETWORKDAYS(C2:C11+0,D2:D11+0),0),0),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Excel formula/syntax question
Replies
4
Views
456
mkrass
M
Jyggalag
  • Solved
Formula to calculate between dates
Replies
4
Views
619
Peter_SSs
Peter_SSs
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
496
etaf
etaf
M
  • Question Question
Change value of cells in a range based on value of another cell
Replies
7
Views
342
mkrass
M
Kellie220
  • Question Question
VBA to Collapse Rows (3 part question)
Replies
1
Views
236
Artik
Artik

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top