Sumifs

mole999

mole999

Moderator
Joined
Oct 23, 2004
Messages
9,879
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:
A

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

mole999

Moderator
Joined
Oct 23, 2004
Messages
9,879
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
 
J

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:
You must log in or register to reply here.

Forum statistics

Threads
1,078,437
Messages
5,340,278
Members
399,362
Latest member
iayb

Share this page

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    • Replies: 37
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    • Replies: 35
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    • Replies: 28
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    • Replies: 24
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    • Replies: 23
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    • Replies: 23
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top