can't figure out how to add cells in ColB if cells in Col A match.

[flyers2thecup]

i'm trying to reconcile two separate reports. one report has a total.
the other, has a bunch of rows that get to a total.

here's an example:

Code
-------------------

COL A COL B
ROW 1 22222 $5.00
ROW 2 11111 $2.50
ROW 3 22222 $1.00
ROW 4 11111 $9.05
ROW 5 22222 $4.55
ROW 6 11111 $3.32


I'm trying to find out the total of all the 22222s and the 11111s
in the list. in this case i would need an output of something like
this:

22222 $10.55
11111 $14.87


-------------------


i can then compare my totals to the totals of the other report.

this is simple when the list is this small. but i have a report o
over 1500 lines.

any ideas? is this even possible?

I'm running XP and have EXCEL 2000.

thanks in advance!!

 

[hgrove]

flyers2thecup wrote...
...

here's an example:
COL A COL B
ROW 1 22222 $5.00
ROW 2 11111 $2.50
ROW 3 22222 $1.00
ROW 4 11111 $9.05
ROW 5 22222 $4.55
ROW 6 11111 $3.32

I'm trying to find out the total of all the 22222s and the 11111s
in the list. in this case i would need an output of something like
this:

22222 $10.55
11111 $14.87

...

Try

=SUMIF($A$1:$A$1500,"11111",$B$1:$B$1500)

and

=SUMIF($A$1:$A$1500,"22222",$B$1:$B$1500

 

[flyers2thecup]

thanks for the response.

however, i can't use a constant as i don't know what they will be.
column A is actually EMPLOYEE ID #s. and the company i work for ha
over 20,000 employees. so you can see why i can't name the cell in th
middle.

i know that i can use a CELL rather than a name of a cell. but wha
that's gonna do is give me duplicate numbers.

ie: if one person has 14 entries, i'll get the total in column "c" 1
different times. if that's the only way to do it, then that's fine, a
this is the closest i've been able to get to making it automated.

again, thanks for the info!!!

 

[Frank Kabel]

Hi
try this small change to Harlans formula (if your IDs are in column 1).
Enter the following in the first row (e.g. C1)
=IF(COUNTIF($A$1:$A1,A1)=1,SUMIF($A$1:$A$1500,A1,$B$1:$B$1500),"")
and copy this down

 

[{*BoB*} OZZIE]

can you sort and subtotal?

How about making the "22222", "11111" a dynamic
reference? sumif(....,cell reference,....) where cell
reference in your formula is the cell that you put the
value you want to add up.