Sum with 2 criteria in column and row

[KIM Chanthan]

Hi,

I have problem to sum base on 2 criteria. One criteria range in row and other criteria range in column. Do you have any way to solve it?

Thanks,

 

[Nebu]

Hi:

one way is to use this array formula

=SUM(IF(($A$4:$A$12=A18)*($C$2:$G$2=B$17),$C$4:$G$12))

Thanks

 

[KIM Chanthan]

Hi:

one way is to use this array formula

=SUM(IF(($A$4:$A$12=A18)*($C$2:$G$2=B$17),$C$4:$G$12))

Thanks

It work perfectly

 

[bosco_yip]

Another non-array formula possible………..

In B18, copy across and down :

=SUMPRODUCT(($A$4:$A$12=$A18)*($C$2:$G$2=B$17),$C$4:$G$12)

Regards
Bosco

 

[KIM Chanthan]

Another non-array formula possible………..

In B18, copy across and down :

=SUMPRODUCT(($A$4:$A$12=$A18)*($C$2:$G$2=B$17),$C$4:$G$12)

Regards
Bosco

Thanks for the formula. I have one more criteria and not sure if you can help or not.
My problem is I want to sum all record in the most left column each week base on region.
Please view attached file for more detail.

Thanks,

 

[bosco_yip]

Thanks for the formula. I have one more criteria and not sure if you can help or not.
My problem is I want to sum all record in the most left column each week base on region.
Please view attached file for more detail.

Thanks,

In B18, formula copy across and down :

=SUMIF($A$4:$A$12,$A18,INDEX($C$4:$G$12,0,INDEX(MATCH(2,1/($C$2:$G$2=B$17)),0)))

Regards