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

  • Thread starter Thread starter flyers2thecup
  • Start date Start date
F

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!!
 
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
Click to expand...
...

Try

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

and

=SUMIF($A$1:$A$1500,"22222",$B$1:$B$1500
 
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!!!
 
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
 
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.
 
Back
Top