Sum with Multilple Criterias at Row & Column

[ygw0707]

Hi, I need help on the below issue.
Other than SUMPRODUCT, what are the other formulas can be used?

Appreciate your kind helping hand

Thank you!!


[TABLE="width: 875"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]

Apple​

[/TD]
[TD]

Apple​

[/TD]
[TD]

Apple​

[/TD]
[TD]

Orange​

[/TD]
[TD]

Orange​

[/TD]
[TD]

Orange​

[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]

Jan-17​

[/TD]
[TD]

Feb-17​

[/TD]
[TD]

Mar-17​

[/TD]
[TD]

Jan-17​

[/TD]
[TD]

Feb-17​

[/TD]
[TD]

Mar-17​

[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD] Sales Volume
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Related Party
[/TD]
[TD]

90,419​

[/TD]
[TD]

33,456​

[/TD]
[TD]

27,548​

[/TD]
[TD]

3,340​

[/TD]
[TD]

4,082​

[/TD]
[TD]

1,902​

[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Third Party
[/TD]
[TD]

458,723​

[/TD]
[TD]

426,393​

[/TD]
[TD]

331,222​

[/TD]
[TD]

40,862​

[/TD]
[TD]

42,643​

[/TD]
[TD]

30,342​

[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD] Total Sales Volume
[/TD]
[TD]

549,141 ​

[/TD]
[TD]

459,849 ​

[/TD]
[TD]

358,770 ​

[/TD]
[TD]

44,201 ​

[/TD]
[TD]

46,725 ​

[/TD]
[TD]

32,244 ​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 14"]Find: Sales volume of Orange to Third Party for YTD Feb-17.
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=VLOOKUP("Third Party",$A$1:$G$6,MATCH("Orange"&"Feb-17",INDEX($A$1:$G$6,1,0)&TEXT(INDEX($A$1:$G$6,2,0),"mmm-yy"),0),0)

 

[Marcelo Branco]

[TABLE="width: 875"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 14"]Find: Sales volume of Orange to Third Party for YTD Feb-17.
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What is the expected result?

M.

 

[ygw0707]

Hi, I will try to avoid using the formula with control+shift+enter as I will need to use the formula very frequently and it will slow down the excel calculating process..

The result would be 83,505 (40,862+42,643).

 

[Aladin Akyurek]

Hi, I will try to avoid using the formula with control+shift+enter as I will need to use the formula very frequently and it will slow down the excel calculating process..

The result would be 83,505 (40,862+42,643).

1. One should always list the expected results for mere mortals among the would-be helpers.

2. The chosen data lay-out seems to suggest array-processing is needed.

Control+shift+enter, not just enter:

=SUM(IF($B$1:$G$1="Orange",IF($B$2:$G$2>=MIN(IF($B$1:$G$1="Orange",$B$2:$G$2)),IF($B$2:$G$2<=DATE(2017,2,1),IF($A$4:$A$5="Third Party",$B$4:$G$5)))))

 

[Marcelo Branco]

Hi, I will try to avoid using the formula with control+shift+enter as I will need to use the formula very frequently and it will slow down the excel calculating process..

The result would be 83,505 (40,862+42,643).

To avoid Ctrl+Shift+enter, maybe...

Dates as dd/mm/yyyy

[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]
[TD="bgcolor: #DCE6F1"]

F​

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

G​

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

1​

[/TD]
[TD][/TD]
[TD]

Apple​

[/TD]
[TD]

Apple​

[/TD]
[TD]

Apple​

[/TD]
[TD]

Orange​

[/TD]
[TD]

Orange​

[/TD]
[TD]

Orange​

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

2​

[/TD]
[TD][/TD]
[TD]

01/01/2017​

[/TD]
[TD]

01/02/2017​

[/TD]
[TD]

01/03/2017​

[/TD]
[TD]

01/01/2017​

[/TD]
[TD]

01/02/2017​

[/TD]
[TD]

01/03/2017​

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

3​

[/TD]
[TD]

Sales Volume​

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

4​

[/TD]
[TD]

Related Party​

[/TD]
[TD]

90419​

[/TD]
[TD]

33456​

[/TD]
[TD]

27548​

[/TD]
[TD]

3340​

[/TD]
[TD]

4082​

[/TD]
[TD]

1902​

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

5​

[/TD]
[TD]

Third Party​

[/TD]
[TD]

458723​

[/TD]
[TD]

426393​

[/TD]
[TD]

331222​

[/TD]
[TD]

40862​

[/TD]
[TD]

42643​

[/TD]
[TD]

30342​

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

6​

[/TD]
[TD]

Total Sales Volume​

[/TD]
[TD]

549141​

[/TD]
[TD]

459849​

[/TD]
[TD]

358770​

[/TD]
[TD]

44201​

[/TD]
[TD]

46725​

[/TD]
[TD]

32244​

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

7​

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

8​

[/TD]
[TD]

Criteria1​

[/TD]
[TD]

Criteria2​

[/TD]
[TD]

Criteria3​

[/TD]
[TD]

Result​

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

9​

[/TD]
[TD]

Third Party​

[/TD]
[TD]

Orange​

[/TD]
[TD]

01/02/2017​

[/TD]
[TD]

83505​

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


Criteria in A9:C9

Formula in D9
=SUMIFS(INDEX($B$4:$G$6,MATCH(A9,$A$4:$A$6,0),0),$B$1:$G$1,B9,$B$2:$G$2,">="&DATE(YEAR(C9),1,1),$B$2:$G$2,"<="&EOMONTH(C9,0))

M.