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


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Total​

[/td][td]

5​

[/td][td]

4​

[/td][td]

6​

[/td][td]

6​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Date​

[/td][td]

02/03/2018​

[/td][td]

03/03/2018​

[/td][td]

04/03/2018​

[/td][td]

05/03/2018​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Apples​

[/td][td]

2​

[/td][td]

3​

[/td][td]

2​

[/td][td]

4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Oranges​

[/td][td]

3​

[/td][td]

1​

[/td][td]

4​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Category​

[/td][td]

From​

[/td][td]

Until​

[/td][td]

Total​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Apples​

[/td][td]

02/03/2018​

[/td][td]

04/03/2018​

[/td][td]

7​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Oranges​

[/td][td]

02/03/2018​

[/td][td]

04/03/2018​

[/td][td]

8​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Total​

[/TD]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[TD]

6​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Date​

[/TD]
[TD]

02/03/2018​

[/TD]
[TD]

03/03/2018​

[/TD]
[TD]

04/03/2018​

[/TD]
[TD]

05/03/2018​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Apples​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Oranges​

[/TD]
[TD]

3​

[/TD]
[TD]

1​

[/TD]
[TD]

4​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Category​

[/TD]
[TD]

From​

[/TD]
[TD]

Until​

[/TD]
[TD]

Total​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Apples​

[/TD]
[TD]

02/03/2018​

[/TD]
[TD]

04/03/2018​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Oranges​

[/TD]
[TD]

02/03/2018​

[/TD]
[TD]

04/03/2018​

[/TD]
[TD]

8​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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