Sum By 1 Criteria

[georgei]

Hi all,

I have a spreadsheet with 2 workbooks, one the first book I have a column with a long list of names and 2nd column with different amounts next to each name. A name will repeat several times and I need to add all the amounts belonging to each name and show in a table in another workbook.

here's an example:

Name               Amount 
------------------------ 
Jonathan          45 
George             60 
Jane                -35 
Jonathan          15 
David               -40 
Jonathan          -20 
Jane                35 
George             50 
David               -30 

So basically what I'm looking for is to create a separate table on another workbook and have Excel list each name in that column only once, and in the next cell show a " total" amount for each person. So for example Jonathan would be listed with 40, George with 110

I tried creating a Pivot Table, but because I have lots of columns and not all of them are filled out for every row I get errors.. "The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns."
and I can't fill each empty cell with a 0 because it causes problems in my formulas.

My spreadsheet goes from A to V columns, name is in A and amount column is V, the last one. There are some empty columns in between, but I need them there.

Is there anything else I can try?
Thanks in advance if you can help!

[StephenR]

You'd be much better off having one "clean" dataset with no completely blank rows or columns as then a pivot table would do this in two seconds. If you must, keep another version with your blanks. That said, if you explain in more detail why you think you need them we might be able to come up with alternatives because your current layout is not good practice.

And attach a sample workbook so we can see for ourselves. And another thing, have a look at the SUMIF formula.

[georgei]

Ok, i'll attach the file. Please take a look.

[StephenR]

OK, I give up. Why can't you just delete those blank rows?

[georgei]

I have more data in them

[StephenR]

I suggest that you read and digest the sticky thead about efficient spreadsheet design.

[maruchan]

Would the SumIf formula work? just using a sumif formula with the names listed

i.e.
=SUMIF(data!A:A,general!C5,data!V:V)

and has a name on the general worksheet in cell C5.

[georgei]

I tried using this SUMIF, but it doesn't seem to work.
Have you tried it on my spreadsheet? Did it work for you? If so, would you mind plugging it into my attached spreadsheet and reattaching it so I can take a look?

Originally Posted by maruchan

Would the SumIf formula work? just using a sumif formula with the names listed

i.e.
=SUMIF(data!A:A,general!C5,data!V:V)

and has a name on the general worksheet in cell C5.

[Dave Hawley]

I tried using this SUMIF, but it doesn't seem to work

How helpful. NOT. Do you tell the Dr you feel unwell and then leave up to him to diagnose you armed with "unwell"?

[georgei]

I went back and tried it a few more times and was able to figure it out.
Thanks, SUMIF works very well.