Sumifs

[mole999]

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

 

[admiral100]

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]

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]

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
	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/2018	09/01/2018	2			Person A	5
3	Person A	AL	05/01/2018	05/01/2018	1
4	Person A	AL	03/01/2018	04/01/2018	2
5	Person A	AL	01/01/2018	03/01/2018	3
Sheet2

Cell Formulas
Range	Formula
I2	=SUMIFS(E2:E4,A2:A4,H2,B2:B4,"AL",C2:C4,">"&TODAY())

With Helper column for FUTURE days:

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

Cell Formulas
Range	Formula
I2	=SUMIFS(F2:F5,A2:A5,H2,B2:B5,"AL")
F2	=IF(C2),D2-TODAY()-1,D2-C2)+1

 

[mole999]

Thanks, used without helper this time