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 then, a button for encoding.


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



then after encoding the data will be encoded on my masterlist, second 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
25*******
26*******
27*******
Masterlist


then this is my problem 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
8*000000000000
9*000000000000
10*000000000000
11*000000000000
12*000000000000
13*000000000000
14*000000000000
15*000000000000
16*000000000000
17*000000000000
18*000000000000
19*000000000000
20*000000000000
21*000000000000
22*000000000000
23*000000000000
24*000000000000
25*000000000000
Delivery Count per Delivery Boy


this is the sample sheet.
in year 2011

Excel Workbook
ABCDEFGHIJKLM
1Delivered bySelect a year2012Summary of Delivery per Delivery Boy
2JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
3Boy 8000001000000
4*000000000000
5*000000000000
6*000000000000
7*000000000000
8*000000000000
9*000000000000
10*000000000000
11*000000000000
12*000000000000
13*000000000000
14*000000000000
15*000000000000
16*000000000000
17*000000000000
18*000000000000
19*000000000000
20*000000000000
21*000000000000
22*000000000000
23*000000000000
24*000000000000
25*000000000000
Delivery per Delivery Boy



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

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
It is not made clear which is the third sheet. I have assumed the the third sheet in the order given as the third sheet.
this data is based on second sheet.
form this second sheet boy 13 delivered in May 2011, 7 items.
how can it be 1

I am sending you sheet 2 and sheet 3

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
Sheet2


Excel Workbook
ABCDEFGHIJKLM
1Delivered bySelect a year2011Summary of Delivery per Delivery Boy
2JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
3Boy 13000700000000
4Boy 2000090000000
5Boy 10000014000000
6Boy 90000140900000
7Boy 40000140000000
Sheet3


see the formula in sheet 3 in B3 which is copied down and right
 
Upvote 0
the sheet 3 are 4 is my third sheet. the fourth is just another example of the third. thank you for the solution, but wheres the formula for the collumn A(the names).???? and i am not looking for the date when it delivers, i am looking for the count on how many the delivery boy delivers in its respective month.
 
Last edited:
Upvote 0
Hello,

Firstly define these names, press Ctrl+F3, click New.

Name: Size
Refers to; =MATCH(REPT("Z",250),Masterlist!$B:$B)
click OK. click New.

Name: DeliMonth
Refers to: =Masterlist!$B$3:INDEX(Masterlist!$B:$B,Size)

Name: DeliPerson
Refers to: =Masterlist!$F$3:INDEX(Masterlist!$F:$F,Size)

Name: DeliYear
Refers to: =Masterlist!$D$3:INDEX(Masterlist!$D:$D,Size)

Go to the sheet 'Delivery Count per Delivery Boy'

A3, copy down. This is an Array Formula. Must hit CONTROL+SHIFT+ENTER, not just ENTER.

Code:
=IFERROR(INDEX(DeliPerson,SMALL(IF(FREQUENCY(IF(DeliYear=$D$1,
MATCH(DeliPerson,DeliPerson,0)),ROW(DeliPerson)-ROW(INDEX(DeliPerson,1,1))+1),
ROW(DeliPerson)-ROW(INDEX(DeliPerson,1,1))+1),ROWS(A$3:A3))),"")
B3, copy across & down, with just ENTER.

Code:
=COUNTIFS(DeliMonth,B$2,DeliYear,$D$1,DeliPerson,$A3)
 
Upvote 0

Forum statistics

Threads
1,224,221
Messages
6,177,217
Members
452,764
Latest member
Mark1963

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