gabrielevdt
New Member
- Joined
- Sep 10, 2015
- Messages
- 3
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.
<tbody>
</tbody>
And below is a summary of the worksheet in which I want the solution to appear.
<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.
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.