Sum data with multiple criteria (column & row)

[jomi9501]

Hi to all, please help provide the formula to sum data with multiple criteria as below

1. Component in column M must match in column A
2. Date in row 2 (column B~J) must be within the date range specified in N1~T2
3. Sum values are in B3~J15


Expected outputs are highlighted in green on attached file.


Thanks in advance.

[Ace_XL]

How about in N3 ..
=SUMIFS($B3:$J3,$B$2:$J$2,">="&N$1,$B$2:$J$2,"<="&N$2)
and drag down and across

[jomi9501]

Hi Ace, thanks for the help. It works since the sum values are within a row like B3~J3 but when I use B3~J15 it gives #VALUE! I need to use the whole sum range B3~J15 because column A & column M may not be exactly alike if given with more data.

[Ace_XL]

In that case use

=SUMIFS(INDEX($B$2:$J$15,MATCH($M3,$A$2:$A$15,0),),$B$2:$J$2,">="&N$1,$B$2:$J$2,"<="&N$2)

[jomi9501]

hi ace, that's great. it did work. thanks