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

xryanx

Board Regular
Joined
Jul 30, 2015
Messages
59
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.


ABCDEFGHIJ
3SP# of Fails$$$SP# of Fails$$$ Total Crackouts2
4RK1 11 Total $$$ $470.00
5DR1 12
6HS 13 Avg Lovgevity 213.5
7BP 14
8JY 15
96 16
107 17
118 18
129 19
1310 20
14
15DateSPLocationDate of RepairReferral #NameVehicleDate of Replace $$$
161/1/2018RKNEL1/1/20176549876549johnson johnson and 2018 ford expedition lkkk12/30/2018$250.00
172/2/2018DRLV12/2/2017321321bobb $220.00

<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>
</tbody>

Spreadsheet Formulas
CellFormula
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>
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,428
Try in C4:
Code:
=sum(if($B$16:$B$20=A4,$J$16:$J$20,0))
entered as an array formula.
 

xryanx

Board Regular
Joined
Jul 30, 2015
Messages
59
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,141
Messages
5,599,966
Members
414,352
Latest member
macquarie_jchan58

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
Top