How do I sum here using criteria there?

[LM]

Ok, imagine column A is a list of social security numbers. Column B is a
list of project names. Column C is a listing of salaries for those social
security numbers. I need to have excel calculate for me each social security
number's total salary.

SSN............Project #......Salary
123456789.....32...........$12,000
123456789.....45...........$15,000
333445555.....31...........$11,000
444556666.....45...........$15,000

So, in this example, you can see that the person with SS# 123456789 worked
on 2 different projects and earned a sum of $27,000. Is there a formula that
I can put into the spreadsheet to do this all the way down? I have over
2,000 records so I can't possibly do this by hand. Thanks.

 

[Alan]

With the SS numbers in A1:A2000 and the money in C1:C2000

=SUMPRODUCT(--($A$1:$A$2000=A1),--($C$1:$C$2000))

Regards,
Alan.

 

[~L]

The sum of salary for all projects by SSN:

=SUMIF($A$1:$A$5000,A1,$C1:$C5000)

 

[LM]

Those formulas don't work?? Here's a more elaborate example of what I'm
looking for:

Columns A (Employee SS), B (Project No), and C (Salary) as follows
A..................B...............C
1..................X..............$5
1..................Y..............$6
2..................X..............$5
2..................Z..............$7
3..................Y..............$5
4..................Y..............$6
4..................Z..............$7
5..................X..............$5
Total............................$46

Desired Result:
A.................B
1.................$11
2.................$12
3.................$5
4.................$13
5.................$5
Total...........$46

Thanks!

 

[Shane Devenshire]

Hi,

I think the solutions you have do work. By the way your example shows both
the raw data and the results in the same columns? Is this a key to why it
doesn't work?

If you data is in D1:F8 for example, then in A1:B5 enter

1 11
2 12
3 5
4 13
5 5

Where the formula in B1 is

=SUMIF(D$1$8,A1,F$1:F$8)

And you copy it down.

 

[~L]

How odd. Both formulas should give you those desired results from the data
you have provided, if you put them in (for example, if you have column
headers in row 1) D2 and change the A1 in those forumulas to A2, then fill
down.

Is the result of both formulas 0, or some number that isn't the right answer
but isn't 0, or an error

If it is 0, is your calculation mode set to automatic? A quick way to check
is to hit F9. To fix this in 2003 go to tools, options, calculation tab, and
make sure the dot is by automatic.

If the answer is wrong, check the SSNs for spaces or other characters.
=TRIM(CLEAN(A2)) in an empty colum then filled down and copy/paste value over
the originals should take care of it.

If it is an error, what is the error?

 

[Ashish Mathur]

Hi,

Just create a simple pivot table. Alternaively, you can use Data > Subtotal
but before this, please sort SSN column in ascending order.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[LM]

I think the problem is that I do not have a list of unique SS numbers to
reference?? My data table is basically a huge list of employee social
security numbers that have various salary numbers that can be attached to
them. If I "reference" cell A1 for example, don't I have to actually have A1
separately identified first? That is my problem. What I would like to do is
to in one step, grab the entire data table and push out the sums attached to
each unique SS no.

I guess it's hard to explain but let me try one more time. My data is a
list of let's say 500 UNIQUE social security numbers that can show up just
once or multiple times in Column A from cell A2:A800. So, SSN 123456789
could be in A1, A2, and A3. While SSN 222334444 could be in A4 and A5. SSN
333445555 could be just in cell A6. So forth and so on. There are
corresponding salary numbers in Column B for each 'A' cell.

With that data, how can I figure up how much in total each SSN earns? For
example, I would need to know how much SSN 123456789 earns in total as found
in cells B1, B2, and B3. Based on the advice given, it looks like I need a
column with each UNIQUE SSN so that I could have the formula reference that
UNIQUE SSN to give me the sum total for that SSN. The problem with this, is
that I don't have a list of the UNIQUE 500 SSN's readily available.

I think my best bet is to use a pivot table but I was hoping there was
formula command that I didn't know of to help me out. Thanks!

 

[Alan]

You can create a list of unique SS numbers quite easily by using Advanced
Filter, Debra Dalgliesh is the expert on this, have a look at her tutorials
on :-

http://www.contextures.com/xladvfilter01.html

Once you have that list, say in E1:E500, use the SUMIF or SUMPRODUCT formula
to get the result

=SUMIF($A$1:$A$5000,E1,$C1:$C5000)

=SUMPRODUCT(--($A$1:$A$5000=E1),--($C$1:$C$5000))

Enter either formula in say F1 and drag it down to the end of the list of SS
numbers.

Unless however you really want to use a formula for clarity or whatever, a
pivot table is the better option.

Regards,

Alan,

 

[Alan]

Better address, http://www.contextures.com/xladvfilter01.html#FilterUR

 

[LM]

Thank you to everyone that posted a response to help me out with this... I
think I have it now thanks to all of you. I truly do appreciate it!