SumIf question

[fozo]

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.

 

[Weazel]

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]

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]

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

	A	B	C	D	E
2	59.52	1093		1089	83.56
3	58.28	1089		1093	91.52
4	32	1093			0
5	25.28	1089			0

<tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula E2 =SUMIF($B$2:$B$5,D2,$A$2) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula 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]

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.