SumIf Index Match Summary Help

**Merlin54k** · 12-21-2011, 01:12 AM

In Excel 2007, I am trying to get the SUM of various cells in range where two criterias are met. One criteria is in a column, the 2nd is in a row.

In Cell E6 I am trying to get the sum from a range of cells (ITC!C4:Q161) where Column A has MTR listed and row 3 has HAN from cells B3:Q3.

MTR can appear on multiple occasions in column A
HAN can appear on multiple occasions in the range from B3:Q3

I placed the formula below in cell E6 with no luck.

=SumIFS(INDEX(ITC!C4:Q161,MATCH('Direct vs Indirect Detail'!C6,ITC!A4:A161,0),MATCH('Direct vs Indirect Detail'!E1,ITC!B3:Q3,0)))

**Haseeb Avarakkan** · 12-21-2011, 02:33 AM

try this E6, copy across,

=SUMPRODUCT((ITC!$D$6:$D$161=$C6)*(ITC!$F$3:$Q$3=E$1),ITC!$F$6:$Q$161)

There are some trailing space at the end in E1, F1, G1, so delete them.

**Merlin54k** · 12-21-2011, 09:05 AM

I plugged this formula on the attached spreadsheet receiving zero as the amount when that is definitely not what it should be. Any suggestions

=SUMPRODUCT((ITC!$A$4:$A$161=$C6)*(ITC!$C$3:$Q$3=E$1),ITC!$C$4:$Q$161)

**Fotis1991** · 12-21-2011, 10:01 AM

Hi

I took a look to your sample and i found one wrong type of word "HAN"

I think your problem is because of this,

Pls take a look to the attachment

Hope to helps ypu.

**Haseeb Avarakkan** · 12-21-2011, 12:46 PM

Originally Posted by Haseeb A

There are some trailing space at the end in E1, F1, G1, so delete them.

You need to delete the trailing spaces. or use this to delete them with formula,

=SUMPRODUCT((ITC!$A$4:$A$161=TRIM($C6))*(ITC!$C$3:$Q$3=TRIM(E$1)),ITC!$C$4:$Q$161)