Sumifs array...

[encioman]

Hello experts,

What formula can I use to get the value:

Name	1-Feb-20	3-Feb-20	10-Feb-20	20-Feb-20	01-Mar-20
Cups	2	5	8	7	12
Spoons	3	1	7	9	8
Cloth	6	3	4	5	11
Plate	10	5	2	4	14

Need to have:

Name​	Date​	Total numbers beyond the given date​
Cloth	3-Feb-20	<what formula which can be copied down..>
Cups	1-Feb-20
Plate	10-Feb-20

Thanks in advance...

God bless

 

[jasonb75]

A couple of methods that will do it,

Book1
	A	B	C	D	E	F
1	Name	01-Feb-20	03-Feb-20	10-Feb-20	20-Feb-20	01-Mar-20
2	Cups	2	5	8	7	12
3	Spoons	3	1	7	9	8
4	Cloth	6	3	4	5	11
5	Plate	10	5	2	4	14
6
7	Name?	Date?	1 way	another way
8	Cloth	03-Feb-20	20	20
9	Cups	01-Feb-20	32	32
10	Plate	10-Feb-20	18	18
Sheet3

Cell Formulas
Range	Formula
C8:D10	C8	=SUMPRODUCT(($A$2:$A$5=$A8)*($B$1:$F$1>$B8)*$B$2:$F$5)

edit:-
xl2bb didn't detect the second formula correctly, the formula in D8 is =IFERROR(SUM(INDEX(INDEX($B$2:$F$5,0,MATCH($B8,$B$1:$F$1)+1):$F$5,MATCH($A8,$A$2:$A$5,0),0)),0)

 

[FormR]

Hi, and another option you could consider.

Book1
	A	B	C	D	E	F
1	Name	01-Feb-2020	03-Feb-2020	10-Feb-2020	20-Feb-2020	01-Mar-2020
2	Cups	2	5	8	7	12
3	Spoons	3	1	7	9	8
4	Cloth	6	3	4	5	11
5	Plate	10	5	2	4	14
6
7	Name	Date
8	Cloth	03-Feb-2020	23
9	Cups	01-Feb-2020	34
10	Plate	10-Feb-2020	20
Sheet1

Cell Formulas
Range	Formula
C8:C10	C8	=SUMIFS(INDEX($B$2:$F$5,MATCH($A8,$A$2:$A$5,0),0),$B$1:$F$1,">="&$B8)

 

[jasonb75]

FormR has identified my ability to overlook the obvious

 

[encioman]

thanks guys... i will try if this will be applicable...