# Sumif/Index/Match

#### Erin Peters

##### New Member
I am using an Index Match formula to match multiple columns by Account #. Then I need to add the payments for those account numbers together from the payment column if they match the account number in the first column. Here is my formula below.
Account #s are in Column A on Sheet 1! and Column A on Sheet 2!. Payments are in Column C on Sheet 2.

=INDEX(Sheet2!\$C2:\$C1473,MATCH('Sheet 1'!\$A2:\$A532,Sheet 2'!\$A2:\$A1473,0))

My issue is it is only giving the first match it finds, not summing them. I have used SUMIF AND SUMIFS, but am not using them correctly.

Thank you

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

##### MrExcel MVP
Like this:

=SUMPRODUCT(SUMIFS(Sheet2!C:C,Sheet2!A:A,Sheet1!\$A2:\$A532))

#### Erin Peters

##### New Member
Aladin, Maybe this makes it easier to understand what I am trying to do. If Column A (Account 1) matches Column B (Account 2), Column B could have multiple rows that match. Then I need these rows to add together for the total for Account # showing in Column A. Thank you for all your help!
Formula producing \$-4,277.00 =INDEX(C2:C1473,MATCH(A2:A1473,B2:B1473,0))
 Account 1 Account 2 Payment Should Be 4167094 4104877 -4,605.93 -4,277.00 -4,317.41 4167485 4125549 27.6 4167651 4125549 -27.6 4176162 4165903 -8,004.00 4176163 4165995 -300 4176172 4166015 -1.77 4176186 4166015 -6.88 4176195 4166015 -43.4 4176250 4166015 -7.09 4176281 4166015 -173.57 4176448 4166015 -27.52 4176469 4166205 -2,269.99 4176533 4166559 -14.87 4176684 4166559 -3,570.00 4176780 4166575 -1,382.23 4176813 4166702 -2,269.99 4176858 4166702 -9.99 4176862 4167094 -4,277.00 4176865 4167094 -40.41 4176875 4167485 -17.29 4176878 4167485 -6,804.00 4176939 4167485 -8.5 4176969 4167651 -81.45 4177051 4167651 -50.38 4177098 4167651 -5,736.00 4177101 4167651 -8.79 4177102 4167651 -201.53 4177175 4167667 -1,068.00 4177193 4167667 -5,736.00 4177203 4167770 -2,268.00 4177339 4167849 -5,736.00 4177341 4167849 5,736.00 4177367 4167849 -5,736.00 4177370 4167849 -8.79 4177519 4168143 -214.89 4177543 4168143 -1,185.00 4177613 4168208 -31.1 4177672 4168208 -2,268.00 4177675 4168208 -7.67 4177678 4168208 -1.77 4177680 4168208 -7.78 4177828 4168208 -2,268.00 4177913 4168208 2,268.00 4177942 4168208 7.67
<colgroup><col width="128" style="width: 96pt; mso-width-source: userset; mso-width-alt: 4681;"> <col width="64" style="width: 48pt;"> <col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;"> <col width="64" style="width: 48pt;" span="3"> <tbody> </tbody>

##### MrExcel MVP
1) Which formulas is producing -4,277.00?

2) What is the relation between Account 1 and Account 2?

3) Which numbers from Payment do add up to -4,317.41? The ones with borders?

#### Tetra201

##### MrExcel MVP
Try the following formula; it returns -4,317.41:

=SUMIF(B:B,A2,C:C)

#### Erin Peters

##### New Member
1) Which formulas is producing -4,277.00?

=INDEX(C2:C1473,MATCH(A2:A1473,B2:B1473,0))

2) What is the relation between Account 1 and Account 2?
-They are specific Patient Account #'s. The ones in Column A(Account 1) were selected for audit purposes and we need to match them with the ones in Column B (Account 2).
-Column C (Payments) payments made to Column B (Account 2).
-I need to match the payments with the Accounts selected for audit, but there may be multiple payments made to the same account #.

3) Which numbers from Payment do add up to -4,317.41? The ones with borders?

#### Erin Peters

##### New Member
3) Which numbers from Payment do add up to -4,317.41? The ones with borders?

 Account 1 Account 2 Payment Should Be 4167094 4104877 -4,605.93 -4,277.00 -4,317.41 4167485 4125549 27.6 4167651 4125549 -27.6 4176162 4165903 -8,004.00 4176163 4165995 -300 4176172 4166015 -1.77 4176186 4166015 -6.88 4176195 4166015 -43.4 4176250 4166015 -7.09 4176281 4166015 -173.57 4176448 4166015 -27.52 4176469 4166205 -2,269.99 4176533 4166559 -14.87 4176684 4166559 -3,570.00 4176780 4166575 -1,382.23 4176813 4166702 -2,269.99 4176858 4166702 -9.99 4176862 4167094 -4,277.00 4176865 4167094 -40.41 4176875 4167485 -17.29
<colgroup><col width="128" style="width: 96pt; mso-width-source: userset; mso-width-alt: 4681;"> <col width="64" style="width: 48pt;"> <col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;"> <col width="64" style="width: 48pt;" span="3"> <tbody> </tbody>

#### Erin Peters

##### New Member
Alright thanks! The =SUMIF(B:B,A2,C:C) worked. I was working with multiple sheets and making it way to complicated...Obviously!

Thanks again for the help!

#### Tetra201

You are welcome.

##### MrExcel MVP
=SUMPRODUCT(SUMIFS(C:C,B:B,\$A\$2:\$A\$21))

would return -4334.7.

The result you want seems to imply a single criterion, that is:

=SUMIFS(C:C,B:B,\$A2)

Replies
10
Views
1K
Replies
1
Views
560
Replies
9
Views
2K
Replies
1
Views
604
Replies
18
Views
833

1,195,619
Messages
6,010,733
Members
441,567
Latest member
Flitbee

### 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.

### Which adblocker are you using?

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

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