sum the range in dates if column condition is true

[pmbt18]

The data entered are as follows :

Total 5 4 6 6
Date 2/3/18 3/3/18 4/3/18 5/3/18
Apples 2 3 2 4
Oranges 3 1 4 2

In the first row, I sum the columns for the number of fruits for the particular date.

I need to display the following :
Category FromDate ToDate Answer
Apples 2/3/18 4/3/18 7

What should I do to get the answer 7?
If I select Oranges, it will display 8.
I tried sumif but I can only sum the total according to the from/to dates but I cannot sum if a category is selected.
Can anyone help??

 

[dave2018]

Excel 2016 (Windows) 32 bit
	A	B	C	D	E
1	Total	5	4	4	8
2	Date	2/3/2018	3/3/2018	4/3/2018	5/3/2018
3	Apples	2	3	2	4
4	Oranges	3	1	2	4
5
6	Category	From	Until	Total
7	Apples	2/3/2018	4/3/2018	7
Sheet4

Cell Formulas
Range	Formula
D7	{=SUM(INDEX(B3:E4,MATCH(A7,A3:A4),N(IF(1,ROW(INDIRECT("A"&MATCH(B7,B2:E2,1)&":A"&MATCH(C7,B2:E2,1)))))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Marcelo Branco]

Maybe something like this...

	A​	B​	C​	D​	E​
1​	Total​	5​	4​	6​	6​
2​	Date​	02/03/2018​	03/03/2018​	04/03/2018​	05/03/2018​
3​	Apples​	2​	3​	2​	4​
4​	Oranges​	3​	1​	4​	2​
5​
6​	Category​	From​	Until​	Total​
7​	Apples​	02/03/2018​	04/03/2018​	7​
8​	Oranges​	02/03/2018​	04/03/2018​	8​
9​

Formula in D7 copied down
=SUMIFS(INDEX($B$3:$E$4,MATCH(A7,$A$3:$A$4,0),0),$B$2:$E$2,">="&B7,$B$2:$E$2,"<="&C7)

M.

 

[dave2018]

Maybe something like this...

	A​	B​	C​	D​	E​
1​	Total​	5​	4​	6​	6​
2​	Date​	02/03/2018​	03/03/2018​	04/03/2018​	05/03/2018​
3​	Apples​	2​	3​	2​	4​
4​	Oranges​	3​	1​	4​	2​
5​
6​	Category​	From​	Until​	Total​
7​	Apples​	02/03/2018​	04/03/2018​	7​
8​	Oranges​	02/03/2018​	04/03/2018​	8​
9​

<tbody>
</tbody>

Formula in D7 copied down
=SUMIFS(INDEX($B$3:$E$4,MATCH(A7,$A$3:$A$4,0),0),$B$2:$E$2,">="&B7,$B$2:$E$2,"<="&C7)

M.

Wow! I always think way too comlicated for myself!! Much better formula than my volatile and roundabout way.

 

[pmbt18]

wow thanks. brilliant!!!

 

[pmbt18]

wow thanks too. u guys r great