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.

K.5.1.3
K.4.1K.5.1.3
K.4.1K.7.1K.5.1.3K.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
1Shows 45,597. I need 45,597+62,192+78,787+95,382 = $281,958
4Shows 96,139. I need $484,126
2Shows 96,139. I need $484,126
5Shows 161,585. I need $745,898
3Shows 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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Here is an easier way of visualizing the formula:


=SUMIF(Top table $B$1:B$6 , Second table $B1 , INDEX(Top table $B$1:$H:$6 ,, MATCH(Second table B$1 , Top table $B$1:$H$1 , 0)))
 
Last edited:
Upvote 0
Try

=SUMPRODUCT((TopTableB1:H1=B1)*(TopTableB2:H2))

Or maybe

=SUMPRODUCT((TopTableB1:H1=B1)*(TopTableA2:A6=A16)*(TopTableB2:H6))

B1=K.5.1.3
A16=1 (4,5,2,3)

Code:
[/FONT][TABLE="width: 520"]
<!--StartFragment--> <colgroup><col width="65" span="8" style="width:65pt"> </colgroup><tbody>[TR]
  [TD="width: 65"][/TD]
  [TD="width: 65"]K.5.1.3[/TD]
  [TD="width: 65"]K.4.1[/TD]
  [TD="width: 65"]K.5.1.3[/TD]
  [TD="width: 65"]K.4.1[/TD]
  [TD="width: 65"]K.7.1[/TD]
  [TD="width: 65"]K.5.1.3[/TD]
  [TD="width: 65"]K.5.1.3[/TD]
 [/TR]
 [TR]
  [TD="align: right"]1[/TD]
  [TD="align: right"]5[/TD]
  [TD="align: right"]6[/TD]
  [TD="align: right"]7[/TD]
  [TD="align: right"]8[/TD]
  [TD="align: right"]9[/TD]
  [TD="align: right"]1[/TD]
  [TD="align: right"]2[/TD]
 [/TR]
 [TR]
  [TD="align: right"]4[/TD]
  [TD="align: right"]4[/TD]
  [TD="align: right"]5[/TD]
  [TD="align: right"]6[/TD]
  [TD="align: right"]7[/TD]
  [TD="align: right"]8[/TD]
  [TD="align: right"]9[/TD]
  [TD="align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="align: right"]5[/TD]
  [TD="align: right"]3[/TD]
  [TD="align: right"]4[/TD]
  [TD="align: right"]5[/TD]
  [TD="align: right"]6[/TD]
  [TD="align: right"]7[/TD]
  [TD="align: right"]8[/TD]
  [TD="align: right"]9[/TD]
 [/TR]
 [TR]
  [TD="align: right"]2[/TD]
  [TD="align: right"]2[/TD]
  [TD="align: right"]3[/TD]
  [TD="align: right"]4[/TD]
  [TD="align: right"]5[/TD]
  [TD="align: right"]6[/TD]
  [TD="align: right"]7[/TD]
  [TD="align: right"]8[/TD]
 [/TR]
 [TR]
  [TD="align: right"]3[/TD]
  [TD="align: right"]1[/TD]
  [TD="align: right"]2[/TD]
  [TD="align: right"]3[/TD]
  [TD="align: right"]4[/TD]
  [TD="align: right"]5[/TD]
  [TD="align: right"]6[/TD]
  [TD="align: right"]7[/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD]K.5.1.3[/TD]
  [TD]K.4.1[/TD]
  [TD]K.7.1[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="align: right"]1[/TD]
  [TD="align: right"]15[/TD]
  [TD="align: right"]14[/TD]
  [TD="align: right"]9[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="align: right"]4[/TD]
  [TD="align: right"]20[/TD]
  [TD="align: right"]12[/TD]
  [TD="align: right"]8[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="align: right"]5[/TD]
  [TD="align: right"]25[/TD]
  [TD="align: right"]10[/TD]
  [TD="align: right"]7[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="align: right"]2[/TD]
  [TD="align: right"]21[/TD]
  [TD="align: right"]8[/TD]
  [TD="align: right"]6[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="align: right"]3[/TD]
  [TD="align: right"]17[/TD]
  [TD="align: right"]6[/TD]
  [TD="align: right"]5[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
<!--EndFragment--></tbody>[/TABLE]
[FONT=Lucida Grande]
 
Last edited:
Upvote 0
Or this:

=SUMPRODUCT((Top Table $A$2:$A$6=Second Table $A2)*(Top Table $B$1:$H$1=Second Table B$1)*(Top Table $B$2:$H$6))
 
Upvote 0
Is that not the same as my 2nd one?

So it is! To be fair, I posted before your post was updated with the second one >.>

Either that or (looking at the time stamps) I must not have refreshed the page again before posting. My apologies.
 
Upvote 0
Aye, must have been my browser, because I remember seeing only one formula on your post.
 
Upvote 0
The second one worked!! I really don't know how to use SUMPRODUCT cause some times you use double negatives and "*", and I thought SUMIFS was like a "better" version of it. But I'll be doing some research on it. Thanks a lot!! :)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top