Adding values of cells based on other cells in the same column

[xryanx]

C4 needs to have the total sum of all Values from J16:J200 that have the value in B16:B200 that is equal to A4.

Example: Because A4 is RK and B16 is RK, C4 will equal $250.
IF I add a new row and B18 is RK and J18 is $250, then C4
will be $500.

Thank you for any help.

	A	B	C	D	E	F	G	H	I	J

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 82px;"><col style="width: 82px;"><col style="width: 82px;"><col style="width: 82px;"><col style="width: 82px;"><col style="width: 82px;"><col style="width: 82px;"><col style="width: 166px;"><col style="width: 82px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: center"]SP[/TD]
[TD="align: center"]# of Fails[/TD]
[TD="align: center"]$$$[/TD]
[TD="align: center"]SP[/TD]
[TD="align: center"]# of Fails[/TD]
[TD="align: center"]$$$[/TD]

[TD="align: center"]Total Crackouts[/TD]
[TD="align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: center"]RK[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"] [/TD]

[TD="align: center"]Total $$$ [/TD]
[TD="align: center"]$470.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: center"]DR[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: center"]HS[/TD]
[TD="align: center"] [/TD]

[TD="align: center"]13[/TD]
[TD="align: center"] [/TD]

[TD="align: center"]Avg Lovgevity [/TD]
[TD="align: center"]213.5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD="align: center"]BP[/TD]
[TD="align: center"] [/TD]

[TD="align: center"]14[/TD]
[TD="align: center"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]
[TD="align: center"]JY[/TD]
[TD="align: center"] [/TD]

[TD="align: center"]15[/TD]
[TD="align: center"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"] [/TD]

[TD="align: center"]16[/TD]
[TD="align: center"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"] [/TD]

[TD="align: center"]17[/TD]
[TD="align: center"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]11[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"] [/TD]

[TD="align: center"]18[/TD]
[TD="align: center"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]12[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"] [/TD]

[TD="align: center"]19[/TD]
[TD="align: center"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]13[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"] [/TD]

[TD="align: center"]20[/TD]
[TD="align: center"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]15[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]SP[/TD]
[TD="align: center"]Location[/TD]
[TD="align: center"]Date of Repair[/TD]
[TD="align: center"]Referral #[/TD]
[TD="colspan: 2, align: center"]Name[/TD]
[TD="align: center"]Vehicle[/TD]
[TD="align: center"]Date of Replace [/TD]
[TD="align: center"]$$$[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]16[/TD]
[TD="align: center"]1/1/2018[/TD]
[TD="align: center"]RK[/TD]
[TD="align: center"]NEL[/TD]
[TD="align: center"]1/1/2017[/TD]
[TD="align: center"]6549876549[/TD]
[TD="colspan: 2, align: center"]johnson johnson and [/TD]
[TD="align: center"]2018 ford expedition lkkk[/TD]
[TD="align: center"]12/30/2018[/TD]
[TD="align: center"]$250.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]17[/TD]
[TD="align: center"]2/2/2018[/TD]
[TD="align: center"]DR[/TD]
[TD="align: center"]LV[/TD]
[TD="align: center"]12/2/2017[/TD]
[TD="align: center"]321321[/TD]
[TD="colspan: 2, align: center"]bobb[/TD]

[TD="align: center"]$220.00[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula I3 =COUNT(A16:A202) B4 =COUNTIF(B16:B200, A4) E4 =COUNTIF(B16:B200, D4) I4 =SUM(J16:J202) B5 =COUNTIF(B16:B200, A5) E5 =COUNTIF(B16:B200, D5) B6 =COUNTIF(B16:B200, A6) E6 =COUNTIF(B16:B200, D6) I6 =SUM(K16:K202)/I3 B7 =COUNTIF(B16:B200, A7) E7 =COUNTIF(B16:B200, D7) B8 =COUNTIF(B16:B200, A8) E8 =COUNTIF(B16:B200, D8) B9 =COUNTIF(B16:B200, A9) E9 =COUNTIF(B16:B200, D9) B10 =COUNTIF(B16:B200, A10) E10 =COUNTIF(B16:B200, D10) B11 =COUNTIF(B16:B200, A11) E11 =COUNTIF(B16:B200, D11) B12 =COUNTIF(B16:B200, A12) E12 =COUNTIF(B16:B200, D12) B13 =COUNTIF(B16:B200, A13) E13 =COUNTIF(B16:B200, D13) <tbody> </tbody>

<tbody>
</tbody>

 

[asad]

Try in C4:

=sum(if($B$16:$B$20=A4,$J$16:$J$20,0))

entered as an array formula.

 

[xryanx]

Thank you, I tried that formula, but it gave me an error "Value". I tried =SUMIF(B16:B200, A4, J16:J200) and that worked. Thank you for your help.

 

[asad]

What ever works for you my friend.