Formula Help

J

jp1984

New Member
Joined
Mar 25, 2010
Messages
1
Okay, I am new to this forum, and seems a great place to learn this program even more :)

I have a spreadsheet that needs to be in the below style - I know for this sort of thing it may not be correct. However, it does have to look like this. It's still very much under construction.

What I want is on this worksheet or another worksheet within the speed sheet the option to find out what is the most profitable day.

What I want to do is be able to have a drop down box with each of the seven days listen and for the user to select it and then be displayed the total amount that that day has produced throughout my data range. So it would need to bring up for example the three Mondays below and calculate the P/L which is the profit and loss in column - I'm using dummy data at the moment.

Basically the data below is a spinet - I will have a whole years worth. I want to see what days seem to do better business as a whole. So If I had 20 Mondays in my data I want a formula that collects the values from column K for Mondays and for it to add together to get a total value.

I've tried Vlookup and obviously that only brings up one value. I know using the add function could do this, but just wondering if there is another way to do this without that mega long add formula

Hope that made sense and some data below

Thanks for any help.





<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 76px;"><col style="width: 97px;"><col style="width: 96px;"><col style="width: 96px;"><col style="width: 96px;"><col style="width: 96px;"><col style="width: 96px;"><col style="width: 96px;"><col style="width: 96px;"><col style="width: 96px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td><td>K</td></tr><tr style="height: 34px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="font-weight: bold; text-align: center;">Day</td><td style="font-weight: bold; text-align: center;">Date</td><td style="font-weight: bold; text-align: center;">Truck Hire </td><td style="font-weight: bold; text-align: center;">Insurance</td><td style="font-weight: bold; text-align: center;">Fuel</td><td style="font-weight: bold; text-align: center;">Staff Cost</td><td style="font-weight: bold; text-align: center;">Sundries</td><td style="font-weight: bold; text-align: center;">Total Expenditure</td><td style="font-weight: bold; text-align: center;">Income</td><td style="font-weight: bold; text-align: center;">P/ L</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>Monday</td><td style="text-align: right;">29/03/2010</td><td style="text-align: center;">£30.63</td><td style="text-align: center;">£50.00</td><td style="text-align: center;">£100.00</td><td style="text-align: center;">£80.00</td><td style="text-align: center;">£2.00</td><td style="text-align: center;">£262.63</td><td style="text-align: center;">£500.00</td><td style="color: rgb(0, 128, 0); font-weight: bold; text-align: center;">£237.37</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td>Tuesday</td><td style="text-align: right;">30/03/2010</td><td style="text-align: center;">£30.63</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£150.63</td><td style="text-align: center;">£30.00</td><td style="color: rgb(255, 0, 0); text-align: center;">-£120.63</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td>Wednesday</td><td style="text-align: right;">31/03/2010</td><td style="text-align: center;">£30.63</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£150.63</td><td style="text-align: center;">£30.00</td><td style="color: rgb(255, 0, 0); text-align: center;">-£120.63</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td>Thursday</td><td style="text-align: right;">01/04/2010</td><td style="text-align: center;">£30.63</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£150.63</td><td style="text-align: center;">£30.00</td><td style="color: rgb(255, 0, 0); text-align: center;">-£120.63</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td>Friday</td><td style="text-align: right;">02/04/2010</td><td style="text-align: center;">£30.63</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£5.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£125.63</td><td style="text-align: center;">£30.00</td><td style="color: rgb(255, 0, 0); text-align: center;">-£95.63</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td>Saturday</td><td style="text-align: right;">03/04/2010</td><td style="text-align: center;">£30.63</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£25.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£145.63</td><td style="text-align: center;">£30.00</td><td style="color: rgb(255, 0, 0); text-align: center;">-£115.63</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td>Sunday</td><td style="text-align: right;">04/04/2010</td><td style="text-align: center;">£30.63</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£150.63</td><td style="text-align: center;">£30.00</td><td style="color: rgb(255, 0, 0); text-align: center;">-£120.63</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td>
</td><td style="font-weight: bold;">Weekly Total</td><td style="font-weight: bold; text-align: center;">£214.41</td><td style="font-weight: bold; text-align: center;">£230.00</td><td style="font-weight: bold; text-align: center;">£275.00</td><td style="font-weight: bold; text-align: center;">£235.00</td><td style="font-weight: bold; text-align: center;">£182.00</td><td style="font-weight: bold; text-align: center;">£1,136.41</td><td style="font-weight: bold; text-align: center;">£680.00</td><td style="color: rgb(255, 0, 0); font-weight: bold; text-align: center;">-£456.41</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td>Monday</td><td style="text-align: right;">05/04/2010</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£50.00</td><td style="text-align: center;">£100.00</td><td style="text-align: center;">£80.00</td><td style="text-align: center;">£2.00</td><td style="text-align: center;">£262.00</td><td style="text-align: center;">£500.00</td><td style="text-align: center;">£238.00</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td>Tuesday</td><td style="text-align: right;">06/04/2010</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£150.00</td><td style="text-align: center;">£30.00</td><td style="color: rgb(255, 0, 0); text-align: center;">-£120.00</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td><td>Wednesday</td><td style="text-align: right;">07/04/2010</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£150.00</td><td style="text-align: center;">£30.00</td><td style="color: rgb(255, 0, 0); text-align: center;">-£120.00</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td><td>Thursday</td><td style="text-align: right;">08/04/2010</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£150.00</td><td style="text-align: center;">£30.00</td><td style="color: rgb(255, 0, 0); text-align: center;">-£120.00</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td><td>Friday</td><td style="text-align: right;">09/04/2010</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£500.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£620.00</td><td style="text-align: center;">£30.00</td><td style="color: rgb(255, 0, 0); text-align: center;">-£590.00</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td><td>Saturday</td><td style="text-align: right;">10/04/2010</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£5.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£125.00</td><td style="text-align: center;">£30.00</td><td style="color: rgb(255, 0, 0); text-align: center;">-£95.00</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td><td>Sunday</td><td style="text-align: right;">11/04/2010</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£150.00</td><td style="text-align: center;">£30.00</td><td style="color: rgb(255, 0, 0); text-align: center;">-£120.00</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">19</td><td>
</td><td style="font-weight: bold;">Weekly Total</td><td style="font-weight: bold; text-align: center;">£210.00</td><td style="font-weight: bold; text-align: center;">£230.00</td><td style="font-weight: bold; text-align: center;">£255.00</td><td style="font-weight: bold; text-align: center;">£730.00</td><td style="font-weight: bold; text-align: center;">£182.00</td><td style="font-weight: bold; text-align: center;">£1,607.00</td><td style="font-weight: bold; text-align: center;">£680.00</td><td style="color: rgb(255, 0, 0); font-weight: bold; text-align: center;">-£927.00</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">20</td><td>
</td><td style="font-weight: bold;">
</td><td style="font-weight: bold;">
</td><td style="font-weight: bold;">
</td><td style="font-weight: bold;">
</td><td style="font-weight: bold;">
</td><td style="font-weight: bold;">
</td><td style="font-weight: bold;">
</td><td style="font-weight: bold;">
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">21</td><td>Monday</td><td style="text-align: right;">12/04/2010</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£50.00</td><td style="text-align: center;">£100.00</td><td style="text-align: center;">£80.00</td><td style="text-align: center;">£2.00</td><td style="text-align: center;">£262.00</td><td style="text-align: center;">£500.00</td><td style="text-align: center;">£238.00</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">22</td><td>Tuesday</td><td style="text-align: right;">13/04/2010</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£150.00</td><td style="text-align: center;">£30.00</td><td style="color: rgb(255, 0, 0); text-align: center;">-£120.00</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">23</td><td>Wednesday</td><td style="text-align: right;">14/04/2010</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£150.00</td><td style="text-align: center;">£30.00</td><td style="color: rgb(255, 0, 0); text-align: center;">-£120.00</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">24</td><td>Thursday</td><td style="text-align: right;">15/04/2010</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£150.00</td><td style="text-align: center;">£30.00</td><td style="color: rgb(255, 0, 0); text-align: center;">-£120.00</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">25</td><td>Friday</td><td style="text-align: right;">16/04/2010</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£500.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£620.00</td><td style="text-align: center;">£30.00</td><td style="color: rgb(255, 0, 0); text-align: center;">-£590.00</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">26</td><td>Saturday</td><td style="text-align: right;">17/04/2010</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£5.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£125.00</td><td style="text-align: center;">£30.00</td><td style="color: rgb(255, 0, 0); text-align: center;">-£95.00</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">27</td><td>Sunday</td><td style="text-align: right;">18/04/2010</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£30.00</td><td style="text-align: center;">£150.00</td><td style="text-align: center;">£30.00</td><td style="color: rgb(255, 0, 0); text-align: center;">-£120.00</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">28</td><td>
</td><td style="font-weight: bold;">Weekly Total</td><td style="font-weight: bold; text-align: center;">£210.00</td><td style="font-weight: bold; text-align: center;">£230.00</td><td style="font-weight: bold; text-align: center;">£255.00</td><td style="font-weight: bold; text-align: center;">£730.00</td><td style="font-weight: bold; text-align: center;">£182.00</td><td style="font-weight: bold; text-align: center;">£1,607.00</td><td style="font-weight: bold; text-align: center;">£680.00</td><td style="color: rgb(255, 0, 0); font-weight: bold; text-align: center;">-£927.00</td></tr></tbody></table><table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>
</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr><tr><td>
</td><td>
</td></tr></tbody></table></td></tr></tbody></table><input class="SYN_LNN" value="2" unselectable="on" type="label"><input id="syn_ctrl2" class="SYN_BCH" role="button" value="-" unselectable="on" type="label">


<input class="SYN_LNN" value="3" unselectable="on" type="label"><input id="syn_ctrl3" class="SYN_BCH" role="button" value="-" unselectable="on" type="label">
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Hi, welcome to the board

I don't think you'll need more than a simple SUMIF to do this
=SUMIF(B:B,"Monday",K:K) for example
(but suggest you keep Monday in a separate cell, so your formula is the same, seven times)

You can use Autofilter to drill down into your source data if you want, so you only see mondays

You can use RANK to highlight the best results

I would personally try to remove the weekly totals to another location, so that you form one single set of data - Excel can pull your results out without having these totals adding complication to your data set - but you suggested you can't do that...
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
482
mumps
mumps
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
79
Dazzybeeguy
D
E
  • Question Question
Adding a custom message.
Replies
1
Views
633
Micron
M
k3yn0t3
  • Solved
INDEX(MATCH()) pulling wrong row data & Macro to duplicate sheets with formulas
Replies
4
Views
544
Alex Blakenburg
A

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top