I need help on what formula to use!!!

doms123

Board Regular
Joined
Apr 19, 2011
Messages
58
Hi I am using excel 2007 and i am having problem on what formula to use on my third sheet...

my first sheet is my Main Sheet where I input data and a button for encoding.


Excel Workbook
ABCDEF
1
2poducts
3
4date delivered
5monthDateYear
6location of delivery
7
8delivered by
9
10sales
11
12Encode
13
Main Sheet


then after encoding the data will be encoded on the second sheet, the Masterlist Sheet.


Excel Workbook
ABCDEFG
1ProductsDate DeliveredLocation of DeliveryDelivered bySales
2MonthDayYear
3Shoes1June82010Area 1Boy 71k
4Shoes2December172010Area 2Boy 21k
5Shoes3August72010Area 3Boy 31k
6Undrewears1September52010Area 5Boy 221k
7Undrewears2October52010Area 5Boy 51k
8Undrewears3November162010Area 2Boy 61k
9Undrewears4December52010Area 3Boy 71k
10Undrewears5June52012Area 2Boy 81k
11Sandals1September252010Area 1Boy 121k
12Sandals2October252010Area 2Boy 101k
13Sandals3November252010Area 3Boy 111k
14Towels1December222010Area 4Boy 71k
15Towels2January252010Area 5Boy 31k
16Towels3June52010Area 2Boy 11k
17Towels4May72011Area 1Boy 131k
18Towels5June52010Area 2Boy 61k
19Towels6August212010Area 2Boy 101k
20Towels7June92011Area 4Boy 21k
21Towels7July142011Area 5Boy 11k
22Shorts1August92011Area 1Boy 91k
23Shorts2June142011Area 5Boy 91k
24Shorts2June142011Area 1Boy 41k
Masterlist Sheet


and my problem is on my third sheet or the Summary of Delivery per Delivery Boy i am having a trouble on what formula to use on Cell A to M particularly starting from rows 3 downward. The scenario is this if i pick a year on cell D1(list down box/cell of years), the count of delivery in respective delivery boy and month will appear. The delivery boy is a name of a true person.

this is the sample sheet.
in year 2011

Excel Workbook
ABCDEFGHIJKLM
1Delivered bySelect a year2011Summary of Delivery per Delivery Boy
2JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
3Boy 13000010000000
4Boy 2000001000000
5Boy 1000000100000
6Boy 9000001010000
7Boy 4000001000000
8000000000000
9000000000000
10000000000000
11000000000000
12000000000000
13000000000000
14000000000000
15000000000000
16000000000000
17000000000000
18000000000000
19000000000000
20000000000000
21000000000000
22000000000000
23000000000000
24000000000000
25000000000000
Delivery per Delivery Boy


here is another example of the third sheet
in year 2012

Excel Workbook
ABCDEFGHIJKLM
1Delivered bySelect a year2012Summary of Delivery per Delivery Boy
2JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
3Boy 8000001000000
4000000000000
5000000000000
6000000000000
7000000000000
8000000000000
9000000000000
10000000000000
11000000000000
12000000000000
13000000000000
14000000000000
15000000000000
16000000000000
17000000000000
18000000000000
19000000000000
20000000000000
21000000000000
22000000000000
23000000000000
24000000000000
25000000000000
Delivery per Delivery Boy



Please please please help me!!!!!!!!!!!
Thank you in advance...
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
I'm not sure I understand your problem, but you might try to setup a pivottable from your Masterlist data. you would then use the getpivotdata formula in each cell in your 'summary of delivery boy' sheet. you can set it up that when you select the different years (I presume that you use a dropdown list) the cells with the getpivotdata formulas will 'get' the figures from your pivottable. it sounds complicated but works great if it is setup correctly.
 
Upvote 0
i think my data is I very detailed(I i select a year, that is located on D1, the name will be displayed on the column A, and the count of delivery on its respective month will also be displayed, that's what i need...), i am not sure on a pivot table because i do not know how to use it, and its way complicated, i am just asking for a formula on what to use on A3:M10000. I am using this is making my project database

The data is linked on the second sheet, my Masterlist. so that if i change the data on the masterlist it will be automatically be updated on the 3rd sheet.

Please please please Help me!!!
Thank you in advance....
 
Upvote 0
Hi,

I can help you get to the following result using a pivot table - this will look like this:

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Year</TD><TD>2010</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD>Count of Delivered by</TD><TD>Column Labels</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD>Row Labels</TD><TD>January</TD><TD>June</TD><TD>August</TD><TD>September</TD><TD>October</TD><TD>November</TD><TD>December</TD><TD>Grand Total</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD>Boy 1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD>Boy 10</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD>Boy 11</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD>Boy 12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD>Boy 2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD>Boy 22</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD>Boy 3</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">12</TD><TD>Boy 5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">13</TD><TD>Boy 6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">14</TD><TD>Boy 7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">15</TD><TD>Grand Total</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">15</TD></TR></TBODY></TABLE>
Sheet7




Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Year</TD><TD>2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD>Count of Delivered by</TD><TD>Column Labels</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD>Row Labels</TD><TD>May</TD><TD>June</TD><TD>July</TD><TD>August</TD><TD>Grand Total</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD>Boy 1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD>Boy 13</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD>Boy 2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD>Boy 4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD>Boy 9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD>Grand Total</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">6</TD></TR></TBODY></TABLE>
Sheet7




Let me know but it will mean unmerging some of youe merged headers on your 2nd sheet........

A formulaic approach is, IMHO, a non starter due the complexity of the formula and the number of vaeiables involved. However if that is your wish, please continue looking for a solution.

Cheers,
Ian
 
Upvote 0

Forum statistics

Threads
1,224,222
Messages
6,177,221
Members
452,765
Latest member
Erka Gizli

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