Sumif/Index/Match

[Erin Peters]

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

 

[Aladin Akyurek]

Like this:

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

 

[Erin Peters]

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>

 

[Aladin Akyurek]

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]

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

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

 

[Erin Peters]

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]

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]

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.

 

[Aladin Akyurek]

=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)