Dropdown tallys by category

[sharmane_stepspd]

Hello,

I have a spreadsheet that tracks type of time worked in .5 hour increments with a dropdown menu that has items from multiple categories. The people using the timesheet would select what type of work they did that half hour and it totals a full day of work. BUT I need to try to create total cells for 3 of the categories. So that at a glance our bookkeeper could say "this person work 2.5 hours on this product, and 1.5 hours on this product and so on. This has to be done over the course of an entire month's worth of work.

My problem is that this is bigger than an sumif formula and I don't have those skills quite yet. Is there a way to have a cell sum if they pull down an item from a particular category?

Thanks in advance for trying to interpret what I wrote here, and then trying to figure out how to do it.

 

[Jerry Sullivan]

Hi and Welcome to the Board,

The summaries you describe can probably be done with worksheet formulas, but it is difficult to give you specific suggestions without seeing the organization of your data.

Please post a screen shot using one of the tools listed below in my signature block.

Also, make sure to note what version of Excel you are using, so helpers will know whether to suggest features like SUMIFS that were not available prior to Excel 2007.

 

[sharmane_stepspd]

Thank you for responding, I didn't see an example near enough to what I am working on to use. I hope I don't violate a rule by posting my actual spread. Here is the basic layout (though I have 5 weeks for a whole month on my actual page) and below that is the list for the drop down. Each 1/2 hour increment would be assigned a category from the drop down menu and then I need to get it to do a grand total (which is easy) and 3 sub totals per category selected (which I haven't figured out yet). DEWA, STEPS, R&D. I feel like I should be able to assign these categories to a subtotal line with a sumif but I researched online and saw many things that said I would need pivot tables (which I have not learned yet).

If this goes too far beyond a quick formula, then I should probably scrap and make a less elegant version at my skill level but I thought I would take a shot and see if anyone out there could help. Thanks!

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-weight: 700; text-align: center; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; }.xl64 { font-weight: 700; text-align: center; border: 0.5pt solid windowtext; }.xl65 { font-weight: 700; vertical-align: middle; border-width: 0.5pt medium 2pt 0.5pt; border-style: solid none double solid; border-color: windowtext -moz-use-text-color windowtext windowtext; }.xl66 { vertical-align: middle; border-width: 0.5pt 0.5pt 2pt medium; border-style: solid solid double none; border-color: windowtext windowtext windowtext -moz-use-text-color; }.xl67 { border-width: 0.5pt medium medium 0.5pt; border-style: solid none none solid; border-color: windowtext -moz-use-text-color -moz-use-text-color windowtext; }.xl68 { border-width: 0.5pt medium medium; border-style: solid none none; border-color: windowtext -moz-use-text-color -moz-use-text-color; }.xl69 { font-weight: 700; text-align: center; border-width: 0.5pt 0.5pt medium; border-style: solid solid none; border-color: windowtext windowtext -moz-use-text-color; }.xl70 { text-align: right; vertical-align: top; border-width: 0.5pt medium 0.5pt 0.5pt; border-style: solid none solid solid; border-color: windowtext -moz-use-text-color windowtext windowtext; white-space: normal; }.xl71 { text-align: left; vertical-align: top; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; white-space: normal; }.xl72 { text-align: center; vertical-align: middle; border-width: 0.5pt 0.5pt medium; border-style: solid solid none; border-color: windowtext windowtext -moz-use-text-color; white-space: normal; }.xl73 { text-align: center; vertical-align: middle; border-width: 0.5pt 0.5pt medium; border-style: solid solid none; border-color: windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% rgb(191, 191, 191); white-space: normal; }.xl74 { white-space: normal; }.xl75 { text-align: right; vertical-align: top; border-width: medium medium medium 0.5pt; border-style: none none none solid; border-color: -moz-use-text-color -moz-use-text-color -moz-use-text-color windowtext; white-space: normal; }.xl76 { text-align: left; vertical-align: top; white-space: normal; }.xl77 { text-align: left; vertical-align: top; border-width: 0.5pt medium; border-style: solid none; border-color: windowtext -moz-use-text-color; white-space: normal; }.xl78 { text-align: left; vertical-align: top; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; white-space: normal; }.xl79 { vertical-align: middle; border-width: 0.5pt medium medium 0.5pt; border-style: solid none none solid; border-color: windowtext -moz-use-text-color -moz-use-text-color windowtext; }.xl80 { vertical-align: middle; border-width: 0.5pt 0.5pt medium medium; border-style: solid solid none none; border-color: windowtext windowtext -moz-use-text-color -moz-use-text-color; }.xl81 { text-align: center; vertical-align: middle; border-width: 0.5pt 0.5pt medium; border-style: solid solid none; border-color: windowtext windowtext -moz-use-text-color; }.xl82 { text-align: left; vertical-align: top; border-width: medium 0.5pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color windowtext -moz-use-text-color -moz-use-text-color; white-space: normal; }.xl83 { vertical-align: middle; border-width: 0.5pt medium medium; border-style: solid none none; border-color: windowtext -moz-use-text-color -moz-use-text-color; }.xl84 { text-align: center; vertical-align: middle; border-width: 0.5pt 0.5pt medium medium; border-style: solid solid none none; border-color: windowtext windowtext -moz-use-text-color -moz-use-text-color; }.xl85 { vertical-align: middle; border-width: 0.5pt medium 2pt; border-style: solid none double; border-color: windowtext -moz-use-text-color; }.xl86 { font-weight: 700; text-align: center; vertical-align: middle; border-width: 0.5pt 0.5pt 2pt medium; border-style: solid solid double none; border-color: windowtext windowtext windowtext -moz-use-text-color; }</style> <table style="border-collapse: collapse; width: 621pt;" width="621" border="0" cellpadding="0" cellspacing="0"> <col style="width: 117pt;" width="117"> <col style="width: 28pt;" width="28"> <col style="width: 38pt;" width="38"> <col style="width: 28pt;" width="28"> <col style="width: 82pt;" span="5" width="82"> <tbody><tr style="height: 14pt;" height="14"> <td colspan="2" class="xl67" style="height: 14pt; width: 145pt;" height="14" width="145"> </td> <td class="xl68" style="width: 38pt;" width="38"> </td> <td class="xl68" style="width: 28pt;" width="28"> </td> <td class="xl64" style="width: 82pt;" width="82">Monday</td> <td class="xl64" style="border-left: medium none; width: 82pt;" width="82">Tuesday</td> <td class="xl64" style="border-left: medium none; width: 82pt;" width="82">Wednesday</td> <td class="xl64" style="border-left: medium none; width: 82pt;" width="82">Thursday</td> <td class="xl63" style="width: 82pt;" width="82">Friday</td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl67" style="height: 14pt;" height="14"> </td> <td class="xl68"> </td> <td class="xl68"> </td> <td class="xl68"> </td> <td class="xl69" style="border-top: medium none;">1/2/12</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">1/3/12</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">1/4/12</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">1/5/12</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">1/6/12</td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl67" style="height: 14pt;" height="14"> </td> <td class="xl68"> </td> <td class="xl68"> </td> <td class="xl68"> </td> <td class="xl69"> </td> <td class="xl69" style="border-left: medium none;"> </td> <td class="xl69" style="border-left: medium none;"> </td> <td class="xl69" style="border-left: medium none;"> </td> <td class="xl69" style="border-left: medium none;"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; width: 117pt;" height="14" width="117">8.00</td> <td class="xl71" style="width: 28pt;" width="28">am</td> <td class="xl70" style="border-left: medium none; width: 38pt;" width="38">8.30</td> <td class="xl71" style="width: 28pt;" width="28">am</td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; border-top: medium none; width: 117pt;" height="14" width="117">8.30</td> <td class="xl71" style="border-top: medium none; width: 28pt;" width="28">am</td> <td class="xl70" style="border-top: medium none; border-left: medium none; width: 38pt;" width="38">9.00</td> <td class="xl71" style="border-top: medium none; width: 28pt;" width="28">am</td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; border-top: medium none; width: 117pt;" height="14" width="117">9.00</td> <td class="xl71" style="border-top: medium none; width: 28pt;" width="28">am</td> <td class="xl75" style="border-left: medium none; width: 38pt;" width="38">9.30</td> <td class="xl71" style="border-top: medium none; width: 28pt;" width="28">am</td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl75" style="height: 14pt; width: 117pt;" height="14" width="117">9.30</td> <td class="xl76" style="width: 28pt;" width="28">am</td> <td class="xl70" style="width: 38pt;" width="38">10.00</td> <td class="xl76" style="width: 28pt;" width="28">am</td> <td class="xl72" style="width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; width: 117pt;" height="14" width="117">10.00</td> <td class="xl77" style="width: 28pt;" width="28">am</td> <td class="xl70" style="border-top: medium none; width: 38pt;" width="38">10.30</td> <td class="xl77" style="width: 28pt;" width="28">am</td> <td class="xl72" style="width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; border-top: medium none; width: 117pt;" height="14" width="117">10.30</td> <td class="xl77" style="border-top: medium none; width: 28pt;" width="28">am</td> <td class="xl70" style="border-top: medium none; width: 38pt;" width="38">11.00</td> <td class="xl77" style="border-top: medium none; width: 28pt;" width="28">am</td> <td class="xl72" style="width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; border-top: medium none; width: 117pt;" height="14" width="117">11.00</td> <td class="xl77" style="border-top: medium none; width: 28pt;" width="28">am</td> <td class="xl70" style="border-top: medium none; width: 38pt;" width="38">11.30</td> <td class="xl77" style="border-top: medium none; width: 28pt;" width="28">am</td> <td class="xl72" style="width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; border-top: medium none; width: 117pt;" height="14" width="117">11.30</td> <td class="xl77" style="border-top: medium none; width: 28pt;" width="28">am</td> <td class="xl70" style="border-top: medium none; width: 38pt;" width="38">12.00</td> <td class="xl77" style="border-top: medium none; width: 28pt;" width="28">am</td> <td class="xl72" style="width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; border-top: medium none; width: 117pt;" height="14" width="117">12.00</td> <td class="xl77" style="border-top: medium none; width: 28pt;" width="28">pm</td> <td class="xl70" style="border-top: medium none; width: 38pt;" width="38">12.30</td> <td class="xl77" style="border-top: medium none; width: 28pt;" width="28">pm</td> <td class="xl72" style="width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; border-top: medium none; width: 117pt;" height="14" width="117">12.30</td> <td class="xl77" style="border-top: medium none; width: 28pt;" width="28">pm</td> <td class="xl70" style="border-top: medium none; width: 38pt;" width="38">1.00</td> <td class="xl77" style="border-top: medium none; width: 28pt;" width="28">pm</td> <td class="xl72" style="width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; border-top: medium none; width: 117pt;" height="14" width="117">1.00</td> <td class="xl77" style="border-top: medium none; width: 28pt;" width="28">pm</td> <td class="xl70" style="border-top: medium none; width: 38pt;" width="38">1.30</td> <td class="xl77" style="border-top: medium none; width: 28pt;" width="28">pm</td> <td class="xl72" style="width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; border-top: medium none; width: 117pt;" height="14" width="117">1.30</td> <td class="xl77" style="border-top: medium none; width: 28pt;" width="28">pm</td> <td class="xl70" style="border-top: medium none; width: 38pt;" width="38">2.00</td> <td class="xl77" style="border-top: medium none; width: 28pt;" width="28">pm</td> <td class="xl72" style="width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; border-top: medium none; width: 117pt;" height="14" width="117">2.00</td> <td class="xl77" style="border-top: medium none; width: 28pt;" width="28">pm</td> <td class="xl70" style="border-top: medium none; width: 38pt;" width="38">2.30</td> <td class="xl77" style="border-top: medium none; width: 28pt;" width="28">pm</td> <td class="xl72" style="width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; border-top: medium none; width: 117pt;" height="14" width="117">2.30</td> <td class="xl78" style="width: 28pt;" width="28">pm</td> <td class="xl70" style="border-top: medium none; border-left: medium none; width: 38pt;" width="38">3.00</td> <td class="xl78" style="width: 28pt;" width="28">pm</td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; border-top: medium none; width: 117pt;" height="14" width="117">3.00</td> <td class="xl78" style="width: 28pt;" width="28">pm</td> <td class="xl70" style="border-top: medium none; border-left: medium none; width: 38pt;" width="38">3.30</td> <td class="xl78" style="width: 28pt;" width="28">pm</td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; border-top: medium none; width: 117pt;" height="14" width="117">3.30</td> <td class="xl77" style="border-top: medium none; width: 28pt;" width="28">pm</td> <td class="xl70" style="border-top: medium none; width: 38pt;" width="38">4.00</td> <td class="xl77" style="border-top: medium none; width: 28pt;" width="28">pm</td> <td class="xl72" style="width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; border-top: medium none; width: 117pt;" height="14" width="117">4.00</td> <td class="xl78" style="width: 28pt;" width="28">pm</td> <td class="xl70" style="border-top: medium none; border-left: medium none; width: 38pt;" width="38">4.30</td> <td class="xl78" style="width: 28pt;" width="28">pm</td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; border-top: medium none; width: 117pt;" height="14" width="117">4.30</td> <td class="xl78" style="width: 28pt;" width="28">pm</td> <td class="xl70" style="border-top: medium none; border-left: medium none; width: 38pt;" width="38">5.00</td> <td class="xl82" style="width: 28pt;" width="28">pm</td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl72" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; border-top: medium none; width: 117pt;" height="14" width="117">5.00</td> <td class="xl77" style="border-top: medium none; width: 28pt;" width="28">pm</td> <td class="xl70" style="border-top: medium none; width: 38pt;" width="38">5.30</td> <td class="xl77" style="width: 28pt;" width="28">pm</td> <td class="xl73" style="width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; border-top: medium none; width: 117pt;" height="14" width="117">5.30</td> <td class="xl78" style="width: 28pt;" width="28">pm</td> <td class="xl70" style="border-top: medium none; border-left: medium none; width: 38pt;" width="38">6.00</td> <td class="xl78" style="width: 28pt;" width="28">pm</td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; border-top: medium none; width: 117pt;" height="14" width="117">6.00</td> <td class="xl78" style="width: 28pt;" width="28">pm</td> <td class="xl70" style="border-top: medium none; border-left: medium none; width: 38pt;" width="38">6.30</td> <td class="xl78" style="width: 28pt;" width="28">pm</td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; border-top: medium none; width: 117pt;" height="14" width="117">6.30</td> <td class="xl78" style="width: 28pt;" width="28">pm</td> <td class="xl70" style="border-top: medium none; border-left: medium none; width: 38pt;" width="38">7.00</td> <td class="xl77" style="border-top: medium none; width: 28pt;" width="28">pm</td> <td class="xl73" style="width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; border-top: medium none; width: 117pt;" height="14" width="117">7.00</td> <td class="xl78" style="width: 28pt;" width="28">pm</td> <td class="xl70" style="border-top: medium none; border-left: medium none; width: 38pt;" width="38">7.30</td> <td class="xl78" style="width: 28pt;" width="28">pm</td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl70" style="height: 14pt; border-top: medium none; width: 117pt;" height="14" width="117">7.30</td> <td class="xl78" style="width: 28pt;" width="28">pm</td> <td class="xl70" style="border-top: medium none; border-left: medium none; width: 38pt;" width="38">8.00</td> <td class="xl78" style="width: 28pt;" width="28">pm</td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> <td class="xl73" style="border-left: medium none; width: 82pt;" width="82"> </td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl79" style="height: 14pt; border-top: medium none;" height="14"> </td> <td class="xl80" style="border-top: medium none;"> </td> <td class="xl83" style="border-top: medium none;"> </td> <td class="xl80" style="border-top: medium none;"> </td> <td class="xl84"> </td> <td class="xl81" style="border-left: medium none;"> </td> <td class="xl81" style="border-left: medium none;"> </td> <td class="xl81" style="border-left: medium none;"> </td> <td class="xl81" style="border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl65" style="height: 15pt;" height="15">Total Hours</td> <td class="xl66"> </td> <td class="xl85"> </td> <td class="xl66"> </td> <td class="xl86">0</td> <td class="xl86">0</td> <td class="xl86">0</td> <td class="xl86">0</td> <td class="xl86">0</td> </tr> </tbody></table>

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { border: 0.5pt solid windowtext; }</style> <table style="border-collapse: collapse; width: 201pt;" width="201" border="0" cellpadding="0" cellspacing="0"> <col style="width: 201pt;" width="201"> <tbody><tr style="height: 14pt;" height="14"> <td class="xl63" style="height: 14pt; width: 201pt;" height="14" width="201">DEWA Course Preparation</td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl63" style="height: 14pt; border-top: medium none;" height="14">DEWA Course Travel</td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl63" style="height: 14pt; border-top: medium none;" height="14">DEWA Course Delivery</td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl63" style="height: 14pt; border-top: medium none;" height="14">DEWA Course Follow Up</td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl63" style="height: 14pt; border-top: medium none;" height="14">STEPS Course Preparation</td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl63" style="height: 14pt; border-top: medium none;" height="14">STEPS Course Travel</td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl63" style="height: 14pt; border-top: medium none;" height="14">STEPS Course Delivery</td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl63" style="height: 14pt; border-top: medium none;" height="14">STEPS Course Follow Up</td> </tr> </tbody></table> <style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { border: 0.5pt solid windowtext; }</style> <table style="border-collapse: collapse; width: 201pt;" width="201" border="0" cellpadding="0" cellspacing="0"> <col style="width: 201pt;" width="201"> <tbody><tr style="height: 14pt;" height="14"> <td class="xl63" style="height: 14pt; width: 201pt;" height="14" width="201">Research & Development Functions</td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl63" style="height: 14pt; border-top: medium none;" height="14">103L Chance & Data R&D</td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl63" style="height: 14pt; border-top: medium none;" height="14">103M Space R&D</td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl63" style="height: 14pt; border-top: medium none;" height="14">119 Grammar R&D</td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl63" style="height: 14pt; border-top: medium none;" height="14">120 Fractions R&D</td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl63" style="height: 14pt; border-top: medium none;" height="14">127 Mental Computation R&D</td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl63" style="height: 14pt; border-top: medium none;" height="14">128 Dr Swan R&D</td> </tr> <tr style="height: 14pt;" height="14"> <td class="xl63" style="height: 14pt; border-top: medium none;" height="14">136 CHPRC R&D
</td> </tr> </tbody></table>

 

[sharmane_stepspd]

Sorry forgot to mention I am on a mac version of Excel 14.1.4

 

[Jerry Sullivan]

That helps- thanks.

So what happens currently when someone wants to record that they worked on "DEWA Course Delivery" from 8:00:Noon;
Took lunch from Noon-1:00, then worked on "STEPS Course Travel" from 1:00-5:00?

Does the worksheet end up showing "DEWA Course Delivery" in 8 Cells for 8:00-Noon?

 

[sharmane_stepspd]

No it just show a grand total of hours, and doesn't acknowledge the subcategories at all. So, if they booked something from 8 AM to 12 PM in DEWA category it would just look like 4 hours in the grand total. Unfortunately, I inherited this worksheet, and before me someone always hand calculated the subcategories which is crazy.

 

[Jerry Sullivan]

I don't understand how categories are applied by the user, and what effect selecting a category has on the worksheet.

Are you using the worksheet now and wanting to add the category totals...or are the categories currently not being selected by the user?

 

[sharmane_stepspd]

Sorry, that I'm not 100%. It's hard for me to explain in a way that even makes sense to myself. I am very grateful you have tried to tackle my issue. Thank you. Also, if after this explain I am still unclear, please don't trouble yourself further. I think I may suggest a different type of time sheet for them.

---
Each half hour cell has a drop down menu, with those options listed in the previous post. And when a item is selected, all it does at present is add .5 hours to the grand total of hours worked in a full day. But I need it to total hours in a day by the three categories. Items that are either DEWA, STEPS, and R&D. So at the bottom of my sheet I would have a DEWA total hours, STEPS total hours, and R&D total hours for each day of the week.

That is why I am having a hard time figuring this out with countif or sumif formulas. Cause I have to account for the person selecting something from a dropdown menu and the fact that the spreadsheet already knows to add .5 hours to the grand total.

I want it to count .5 hours if for example "DEWA course preparation" is selected (in one cell) and it would total in DEWA total cell. But there are a couple other DEWA options. So ALL items selected that have DEWA in the title would need to be totaled in the same cell. Does that make better sense?

Thanks again.

 

[vaskov17]

=COUNTIF(A1:A20,"*research & development*")*0.5

the above formula will count the number of all cells in the range A1:A20 that have "research & development" in them, then multiply it by 0.5 to get the number of 30 minute periods.

Change the range and the words you are searching for to make it work wherever you want. Also make sure to keep the * inside the quotes"

 

[sharmane_stepspd]

UGH the *'s forgot about them. That's it, thank you so much!!!

I was thinking all wrong but as soon as you posted it, it made sense. Thanks for all your help. I really appreciate it.