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.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]K.5.1.3
[/TD]
[TD]K.4.1[/TD]
[TD]K.5.1.3
[/TD]
[TD]K.4.1[/TD]
[TD]K.7.1[/TD]
[TD]K.5.1.3[/TD]
[TD]K.5.1.3
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]$45,597
[/TD]
[TD]$2,533[/TD]
[TD]$62,192
[/TD]
[TD]$228[/TD]
[TD]$4,987[/TD]
[TD]$78,787[/TD]
[TD]$95,382
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]$96,139
[/TD]
[TD]$4,564[/TD]
[TD]$112,734
[/TD]
[TD]$714[/TD]
[TD]$895[/TD]
[TD]$129,329[/TD]
[TD]$145,924
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]$96,139
[/TD]
[TD]$7,898[/TD]
[TD]$112,734
[/TD]
[TD]$987[/TD]
[TD]$1,484[/TD]
[TD]$129,329[/TD]
[TD]$145,924
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]$161,582
[/TD]
[TD]$900[/TD]
[TD]$178,177
[/TD]
[TD]$423[/TD]
[TD]$764[/TD]
[TD]$194,772[/TD]
[TD]$211,367
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]$50,543
[/TD]
[TD]$1,450[/TD]
[TD]$67,138
[/TD]
[TD]$455[/TD]
[TD]$631[/TD]
[TD]$83,733[/TD]
[TD]$100,328
[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]K.5.1.3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Shows 45,597. I need 45,597+62,192+78,787+95,382 = $281,958[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Shows 96,139. I need $484,126[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Shows 96,139. I need $484,126[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Shows 161,585. I need $745,898[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Shows 50,543. I need $301,742[/TD]
[/TR]
</tbody>[/TABLE]

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.