Sumifs

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,108
Office Version
2019, 2016, 2013
Platform
Windows
FULL NAMEABS_SHORTFirstLastdaysxxWhoSUM
Person AAL08/01/201809/01/20182 Person A0
Person AAL05/01/201805/01/20181
Person AAL03/01/201804/01/20182


<tbody>

</tbody>
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:

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

admiral100

Well-known Member
Joined
Jan 17, 2015
Messages
873
Hi,

Maybe this :

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

ABCDEFGHI
1FULL NAMEABS_SHORTFirstLastdaysxxWhoSUM
2Person AAL08/01/1809/01/182Person A5
3Person AAL05/01/1805/01/181
4Person AAL03/01/1804/01/182

<colgroup><col><col><col><col span="7"></colgroup><tbody>
</tbody>
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,108
Office Version
2019, 2016, 2013
Platform
Windows
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
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">FULL NAME</td><td style=";">ABS_SHORT</td><td style=";">First</td><td style=";">Last</td><td style=";">days</td><td style=";">x</td><td style=";">x</td><td style=";">Who</td><td style=";">SUM</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Person A</td><td style=";">AL</td><td style="text-align: right;;">08/01/2018</td><td style="text-align: right;;">09/01/2018</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Person A</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Person A</td><td style=";">AL</td><td style="text-align: right;;">05/01/2018</td><td style="text-align: right;;">05/01/2018</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Person A</td><td style=";">AL</td><td style="text-align: right;;">03/01/2018</td><td style="text-align: right;;">04/01/2018</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Person A</td><td style=";">AL</td><td style="text-align: right;;">01/01/2018</td><td style="text-align: right;;">03/01/2018</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I2</th><td style="text-align:left">=SUMIFS(<font color="Blue">E2:E4,A2:A4,H2,B2:B4,"AL",C2:C4,">"&TODAY(<font color="Red"></font>)</font>)</td></tr></tbody></table></td></tr></table><br />

With Helper column for FUTURE days:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">FULL NAME</td><td style=";">ABS_SHORT</td><td style=";">First</td><td style=";">Last</td><td style=";">Total days</td><td style=";">Future days</td><td style=";">x</td><td style=";">Who</td><td style=";">SUM</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Person A</td><td style=";">AL</td><td style="text-align: right;;">08/01/2018</td><td style="text-align: right;;">09/01/2018</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style=";">Person A</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Person A</td><td style=";">AL</td><td style="text-align: right;;">05/01/2018</td><td style="text-align: right;;">05/01/2018</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Person A</td><td style=";">AL</td><td style="text-align: right;;">03/01/2018</td><td style="text-align: right;;">04/01/2018</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Person A</td><td style=";">AL</td><td style="text-align: right;;">01/01/2018</td><td style="text-align: right;;">03/01/2018</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I2</th><td style="text-align:left">=SUMIFS(<font color="Blue">F2:F5,A2:A5,H2,B2:B5,"AL"</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=IF(<font color="Blue">C2<TODAY(<font color="Red"></font>),D2-TODAY(<font color="Red"></font>)-1,D2-C2</font>)+1</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,108
Office Version
2019, 2016, 2013
Platform
Windows
Thanks, used without helper this time
 

Watch MrExcel Video

Forum statistics

Threads
1,090,234
Messages
5,413,226
Members
403,468
Latest member
adenard

This Week's Hot Topics

Top