Sumifs

mole999

mole999

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
[TABLE="width: 5, align: left"]

<tbody>[TR]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] "]FULL NAME[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] "]ABS_SHORT[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] "]First[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] "]Last[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] "]days[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] "]x[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] "]x[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] "]Who[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] "]SUM[/TD]

[/TR]

[TR]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] "]Person A[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] "]AL[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] "]08/01/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] "]09/01/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] "]Person A[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"]0[/TD]

[/TR]

[TR]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] "]Person A[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] "]AL[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] "]05/01/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] "]05/01/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]

[/TR]

[TR]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] "]Person A[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] "]AL[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] "]03/01/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] "]04/01/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]

[/TR]

</tbody>[/TABLE]
need to put into SUM, the total leave days that are in the future. i.e. yesterday being the 1st, I want the total sum for the person identified. my SUMIFS construction sucks, keep geeting 0 displayed which is obviously wrong
 
Last edited:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Hi,

Maybe this :

I2 =SUMPRODUCT(--($A$2:$A$4=H2),--($C$2:$C$4>TODAY()),$E$2:$E$4)

[TABLE="width: 738"]
<colgroup><col><col><col><col span="7"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]FULL NAME[/TD]
[TD]ABS_SHORT[/TD]
[TD]First[/TD]
[TD]Last[/TD]
[TD]days[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Who[/TD]
[TD]SUM[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Person A[/TD]
[TD]AL[/TD]
[TD]08/01/18[/TD]
[TD]09/01/18[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Person A[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Person A[/TD]
[TD]AL[/TD]
[TD]05/01/18[/TD]
[TD]05/01/18[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Person A[/TD]
[TD]AL[/TD]
[TD]03/01/18[/TD]
[TD]04/01/18[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
That worked well, I forget that I need to check that B = AL, due to other codes that maybe pulled and this is only to see booked Annual Leave until year end, from that already taken
 
Upvote 0
Hi Mole,

I was thinking what if the "First" day is in the PAST, but the "Last" day is in the FUTURE as in my sample in Row 5.
Shouldn't the Future days still be added?

If so (and I'm assuming today don't count and we're ONLY counting FUTURE days), I think the easiest way is to add a Helper Column for FUTURE days, otherwise it'll probably need an array formula.

Using SUMIFS without Helper Column, incorrect answer is given:


Book1
ABCDEFGHI
1FULL NAMEABS_SHORTFirstLastdaysxxWhoSUM
2Person AAL08/01/201809/01/20182Person A5
3Person AAL05/01/201805/01/20181
4Person AAL03/01/201804/01/20182
5Person AAL01/01/201803/01/20183
Sheet2
Cell Formulas
RangeFormula
I2=SUMIFS(E2:E4,A2:A4,H2,B2:B4,"AL",C2:C4,">"&TODAY())


With Helper column for FUTURE days:


Book1
ABCDEFGHI
1FULL NAMEABS_SHORTFirstLastTotal daysFuture daysxWhoSUM
2Person AAL08/01/201809/01/201822Person A6
3Person AAL05/01/201805/01/201811
4Person AAL03/01/201804/01/201822
5Person AAL01/01/201803/01/201831
Sheet3
Cell Formulas
RangeFormula
I2=SUMIFS(F2:F5,A2:A5,H2,B2:B5,"AL")
F2=IF(C2),D2-TODAY()-1,D2-C2)+1
 
Last edited:
Upvote 0
Thanks, used without helper this time
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Count filtered unique values where sum of values in another column meets criteria
Replies
9
Views
1K
excelhelppleas
E
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
P
  • Solved
Computing investment return formula
Replies
8
Views
674
polarwoc
P
L
  • Solved
SUMIFS based on multiple criteria and between date
2
Replies
17
Views
921
etaf
etaf
Rob_010101
  • Solved
Complicated SUMIFS/COUNTIFS Formulas Required
Replies
5
Views
685
etaf
etaf

Forum statistics

Threads
1,223,911
Messages
6,175,326
Members
452,635
Latest member
laura12345

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