Sumifs

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,281
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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

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,281
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,281
Office Version
2019, 2016, 2013
Platform
Windows
Thanks, used without helper this time
 

Watch MrExcel Video

Forum statistics

Threads
1,095,413
Messages
5,444,319
Members
405,278
Latest member
karen1

This Week's Hot Topics

Top