Sum of Columns if Values of one Column are the Same - For Large Spreadsheets

[cmeeker]

Hi there,

I'm in need of a formula that can give me the sum of cells in a column if the values in another column are the same. For example:


Col A
John Smith
John Smith
John Smith
Jane Doe
Jane Doe
Jane Doe

Col B
2
8
9
3
4
6

Col E
Monday
Tuesday
Sunday
Monday
Wednesday
Friday

The total for John Smith would be 19 and the total for Jane Doe would be 13. The problem is that this file is huge and we will have to use this formula on a weekly basis with different values each week, so it would be very difficult to use a "specific" formula for each person. Is this a possibility?

And to make it more difficult, I would then have to subtract one of those values if that row has a certain value in one of the other columns.

If values in Column A are equal to each other, sum of Column B. (Column C would contain the sum in this case.)

Column D = Column C minus B, if value of Column E is Sunday, otherwise don't subtract anything.

The total for John Smith in Column D would be 10 and Jane's total for D would still be 13.



I hope this makes sense and didn't confuse anyone out there. Please advise.

Thanks!

C.M.

[tuph]

It would help if you could upload a copy of your workbook so we can test solutions without having to retype your data. Remember to remove/replace any sensitive information before uploading.

[sktneer]

For the first part it is very easy to get the total for each person. You can use either SUMIF or SUMPRODUCT functions.
But to get the total on the basis of weekly data, formula will depend on the sheet layout i.e. how do you arrange your data in the sheet.
So to get the proper help from the forum, it would be better if you upload a sample workbook with a layout which is exactly same as your original workbook with enough sample data in it.

[cmeeker]

How would I upload my file?

[tuph]

After you click Reply click Go Advanced. Below your reply window click on the button to Manage Attachments.

[sktneer]

There are two ways to do this.......

1) Click Edit (your first post) --> Go Advanced --> Click the paper clip icon just above the reply box (where you will type your reply) --> Add Files --> Browse --> Upload --> Done (on the bottom right of the window) --> Post

2) While you reply to the thread, click on Go Advanced --> follow all the same steps as in 1.

[cmeeker]

test.xls

I hope this works.

[MarvinP]

Hi C.M. and welcome to the forum,

This looks like a very basic Pivot Table problem/answer to me. See the attached with some sample data and what I mean. Time to learn Pivot Tables?

[cmeeker]

Hi C.M. and welcome to the forum,

This looks like a very basic Pivot Table problem/answer to me. See the attached with some sample data and what I mean. Time to learn Pivot Tables?

This would probably work, but the names sometimes change from week to week. It is for payroll purposes. Some weeks there will be 30 people and some weeks there might be over 100. Would this be something in which we would have go through and add or delete names as needed? Or would I be able to copy and paste whatever is needed to make a pivot table, into each excel file that is submitted to me? I'm not very good with Excel anymore. :/ Also, the reason why I need Sunday subtracted from the total is because it is paid at a different rate type, so it would be entered into my program separately. I would need one total with Sunday and one with everything but Sunday. Is this possible?

Thank you for helping.

[MarvinP]

All your requirements are possible using Pivot Tables. They are very easy to make with 2010 Excel. Start learning how to work with them.

http://www.addictivetips.com/windows...-pivot-tables/

There are also LOTS of YouTube tutorials online about Pivots.

[cmeeker]

All your requirements are possible using Pivot Tables. They are very easy to make with 2010 Excel. Start learning how to work with them.

http://www.addictivetips.com/windows...-pivot-tables/

There are also LOTS of YouTube tutorials online about Pivots.

Thank you so much. You are very helpful.