Sum of numbers if 2 columns match

**carllj** · 02-01-2013, 01:50 PM

Hello everyone, I have been using quite a few of your suggestions recently and I was wondering if you could help me with a problem I am having.
I have a large set of data that I need to put into an expense report. Column B has the unit#, Column D has the failure type, and Column C is the price. There are about 13500 lines, and I need a formula that will add column C if B and D are equal. What would be the best way to accomplish this?

**dredwolf** · 02-01-2013, 04:41 PM

Assuming you want "IF B1=D1 then add C1 to the total,IF B2=D2 then add C2 to the total..."
you could try this
=SUMPRODUCT(--($B$1:$B$13500=$D$1:$D$13500),$C$1:$C$13500)

Hope this helps

**carllj** · 02-01-2013, 06:35 PM

What i want is more like this:

Unit Price Reason
1 12 Repair
1 10 Repair
1 13 maintenance
1 15 maintenance
2 12 Repair
2 10 Repair
2 13 Maintenance
2 15 Maintenance

I want to add all the repairs for 1 together and all the maintence together, then do the same for 2, and so on and so forth, with 6 different reason codes and about 150 units.

**dredwolf** · 02-01-2013, 06:58 PM

try this then,
For Unit1

Formula:

Please Login or Register  to view this content.

the red number is the one to change for each unit check, if the only values in column D are Repair or Maintenance,then you could use

Formula:

Please Login or Register  to view this content.

and if you wanted the results in a set spot say F6-F11:
In F6:

Formula:

Please Login or Register  to view this content.

or

Formula:

Please Login or Register  to view this content.

and drag down to F11

Edit-
you could go across columns as well using same idea, swap Columns for rows, and change the range..

Hope this helps

Edit 2 -
Sorry, mis read Bottom of Post...Please ignore the shorter formulas...

**dredwolf** · 02-01-2013, 08:11 PM

Just Thought I'd upload a sample showing the formula in use

This is not quite how you'd do it in a final product, you'd add error checking to make sure the two reasons were different, etc..
But does show how it could be used