SumIf question

fozo

New Member
Joined
Mar 13, 2013
Messages
24
Hello everyone,

I have 2 columns:

Column A: list various prices
Column B: list various job numbers that associate to the price on column A

Goal: to come up with a total for each job and also label the job number itself.

For example:
Column A Column B
59.52 1093
58.28 1089
32.00 1093
25.28 1089

Result: (formula)

1093: sum
1089: sum

I know the sumif formula but I still have to specify the job number for every formula and need to label the job manually. So, I am wondering if there is a way to write a formula for such request?

Thanks so much.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
is it feasible to copy the job numbers to a different column and just remove duplicates?
This would give you a unique list to use with the sumif
 

fozo

New Member
Joined
Mar 13, 2013
Messages
24
job numbers are in column B. And how are you going to remove the duplicate to get the result? Each job number (in col. B) connects to each price (col. A). So, if you remove the duplicate job in col. B, how are you going to sum up the correct for each job?
 

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
you would just copy the job no. column to a new column then remove duplicates.

or as long as the job #'s are all numeric maybe something like....

Excel 2012
ABCDE
259.521093108983.56
358.281089109391.52
43210930
525.2810890

<tbody>
</tbody>
Sheet4

Worksheet Formulas
CellFormula
E2=SUMIF($B$2:$B$5,D2,$A$2)

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
D2{=IFERROR(SMALL(IF(FREQUENCY($B$2:$B$5,$B$2:$B$5),$B$2:$B$5),ROWS($D$2:D2)),"")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>


or if they're not then maybe...

=INDEX($B$2:$B$5,SMALL(IF(FREQUENCY(IF($B$2:$B$5<>"",MATCH($B$2:$B$5,$B$2:$B$5,0)),ROW($B$2:$B$5)-ROW($B$2)+1),ROW($B$2:$B$5)-ROW($B$2)+1),ROWS($D$19:D19)))

I think

=IFERROR(INDEX($B$2:$B$5,MATCH(0,INDEX(COUNTIF($D$1:D1,$B$2:$B$5),,),0)),"")

would also get you what you want
 

Gh0stR1der

New Member
Joined
Feb 3, 2014
Messages
2
What Weazel is saying is in a separate area away from this data set, you copy the whole list of job numbers to there. With the "Remove Duplicates" function on the Data tab, you remove all the duplicates thus yielding only unique job numbers. From there, on the column next to this now-unique list of job numbers, write your sumif for each job number to sum up the prices.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,567
Messages
5,596,908
Members
414,110
Latest member
docops

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