Need help with summing a variable number of cells

[loolala]

Thanks for looking in advance, guys. Just now made an account to specifically get help with this issue. I run a small business buying and selling used electronics (mostly video games) and I keep track of the numbers using a spreadsheet that I've customized over the years. Right now, I have no way to tell the profit from each deal except for summing everything in the deal, as shown here.
Screen Shot 2013-06-28 at 5.10.17 PM.jpg


After re-evaluating the purpose of the spreadsheet I realized the profit from each deal is crucial information that I think should be readily displayed and obvious.

Something similar to this: Screen Shot 2013-06-28 at 5.08.09 PM.jpg


However, because each deal contains a variable amount of items I'm having difficulty figuring out a way to automate this. I'm considering using the rudimentary copy and paste and just summing it myself with the mouse but I wanted to reach out and see if there was a better, more efficient way. Thanks in advance for the time and the help.

James

[FDibbins]

Hi and welcome to the forum

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you

[loolala]

My apologies for uploading images. I've uploaded a very basic deal showing my layout. The profit cell after the first deal is what I want after every deal but because each deal contains a different number of items I'm unsure of any other way to include the profit cell other than manually entering it every time. I have a cell already that tracks the running total but I want the profit from each individual deal to be displayed as well.

sample.xlsx

[FDibbins]

Thanks for the file. What constitutes a "deal"? There doesnt seem to be any logic or connection between items you have "grouped" in a deal?

I had thought at 1st that a sum by date would work, but apparently not....

[loolala]

Right now,the only thing that separates a deal is the single cell in between the bundle. That's also something I wanted to work out--how best to separate each deal/bundle. I've made the row red to denote separation between each deal.

To take a step back, really I just want structural/formatting advice on how to improve this spreadsheet and how to makeand automate a profit cell that would auto sum all items in that column up to a certain amount (would be different depending on how many items are in that deal)

If the profit cell request is too ridiculous (I have a feeling it is) any structural formatting advice would be appreciated.


I've attached a more thorough sample of what my entire spreadsheet currently looks like.
sample.xlsx


Thanks.

[FDibbins]

Based on your 2nd file, what would constitute a "deal"? If it is already shown, apologies if I didnt see it

[loolala]

I realize it can be a little unclear and I apologize. Here is another sample where I've written in the text to show the end of each deal. Right now, I have just an empty row separating the deals and I realize that's a pretty bad way to differentiate them but I have yet to find a better methord.

And I've put the desired profit cell under each deal--that's what I'm trying to automate.

sample.xlsx

[FDibbins]

OK I will take a look at it for you. perhaps if you included an extra column (a helper column) and inserted where each Deal" ended, we could use that to determine the sum?

[loolala]

Can you expand on that idea give an example? I'm a little confused.

[FDibbins]

Take a look at the attached. You can do away with the "Deal" row, and just put an X in column J when a "deal" ends, K will keep a running total per deal, and then restart for each new deal

[loolala]

Wow this is not a bad solution, using the IF statements. Is there any way to hide the running total and just display the final amount?

[FDibbins]

Sure, try this little trick using Conditional Formatting...

1. highlight the range you want to apply the conditional formatting to (K2:K109 in the file you gave me, you will probably need to adjust the range)
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =$J2<>"x" format FONT color WHITE
This will make all values that do NOT appear next to X, invisible

[loolala]

Awesome thanks so much!

[FDibbins]

Happy to help

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[loolala]

Hey--tried applying the conditional format rule but am running into an error. I have Excel 2003 so I went into the conditional formatting tool then changed it to "formula is" and typed in the formula you gave me but it's saying it contains an error. Any suggestions?