Complex SUMIF formula

[gabrielevdt]

Hi,

I'm trying to use a SUMIF formula in which the criteria is in one column but it has multiple sum ranges determined by the values in a row. Below is a summary of the worksheet from which the formula is being linked. The first column (numbers 1 - 5) and top row (K.5.1.3) represent the criteria.

	K.5.1.3	K.4.1	K.5.1.3	K.4.1	K.7.1	K.5.1.3	K.5.1.3
1	$45,597	$2,533	$62,192	$228	$4,987	$78,787	$95,382
4	$96,139	$4,564	$112,734	$714	$895	$129,329	$145,924
5	$96,139	$7,898	$112,734	$987	$1,484	$129,329	$145,924
2	$161,582	$900	$178,177	$423	$764	$194,772	$211,367
3	$50,543	$1,450	$67,138	$455	$631	$83,733	$100,328

<tbody>
</tbody>

And below is a summary of the worksheet in which I want the solution to appear.

	K.5.1.3
1	Shows 45,597. I need 45,597+62,192+78,787+95,382 = $281,958
4	Shows 96,139. I need $484,126
2	Shows 96,139. I need $484,126
5	Shows 161,585. I need $745,898
3	Shows 50,543. I need $301,742

<tbody>
</tbody>

The formula I tried is:

=SUMIF($First column of top table$ , Corresponding cell in first column of 2nd table , INDEX($All dollar figures of first table$ ,, MATCH(Top right cell from 2nd table , Top row 1st table , 0)))

I know I could link the 2nd table to a new Total column, but I'M NOT ALLOWED ADD ANY COLUMNS OR ROWS. Basically I have to link it with the worksheet as is (It doesn't has to be a SUMIF, though).

I've been fighting with this for hours! I would really appreciate any tip.

PS. This is summary; the real worksheets has hundreds of rows and columns. I have to do the same thing for K.4.1, K.7.1, etc.

 

[dreid1011]

I've learned that SUMIFS don't usually work well when your data is in that layout. For this, you use SUMPRODUCT. SUMIFS like the data to be up and down in columns.

 

[gaz_chops]

You're welcome.