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.

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.
"LM" wrote in message
...
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.

The sum of salary for all projects by SSN:

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

"LM" wrote:

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.

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!

"~L" wrote:

The sum of salary for all projects by SSN:

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

"LM" wrote:

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.

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:D$8,A1,F$1:F$8)

And you copy it down.



--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"LM" wrote:

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!

"~L" wrote:

The sum of salary for all projects by SSN:

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

"LM" wrote:

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.

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?


"LM" wrote:

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!

"~L" wrote:

The sum of salary for all projects by SSN:

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

"LM" wrote:

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.

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" wrote in message
...
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.

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!

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,




"LM" wrote in message
...
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!

Better address, http://www.contextures.com/xladvfilter01.html#FilterUR
"Alan" wrote in message
...
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,




"LM" wrote in message
...
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!

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!

"Alan" wrote:

Better address, http://www.contextures.com/xladvfilter01.html#FilterUR
"Alan" wrote in message
...
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,




"LM" wrote in message
...
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!