# Sumifs

#### mole999

##### Moderator
 FULL NAME ABS_SHORT First Last days x x Who SUM Person A AL 08/01/2018 09/01/2018 2 Person A 0 Person A AL 05/01/2018 05/01/2018 1 Person A AL 03/01/2018 04/01/2018 2

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

### 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.

##### Well-known Member
Hi,

Maybe this :

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

 A B C D E F G H I 1 FULL NAME ABS_SHORT First Last days x x Who SUM 2 Person A AL 08/01/18 09/01/18 2 Person A 5 3 Person A AL 05/01/18 05/01/18 1 4 Person A AL 03/01/18 04/01/18 2

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

#### mole999

##### Moderator
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
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:

With Helper column for FUTURE days:

Last edited:

#### mole999

##### Moderator
Thanks, used without helper this time