Date and Countif Week Formula

searchingforhelp

New Member
Hi,

Need help in expanding the formulas and have them automatically populate when dragging column A downward in the Weekly Performance tab (second image). In the Weekly Performance tab, the dates in column A and B are pulled from the Data tab (first image) column A and B. When dragging the Weekly Performance column A downward, only column C and D populate. Is there a formula or set of formulas when dragging column column A downward, to have B through H populate with what is in the Data tab?

Lastly is there a way I can compensate for holidays that land on a Monday? If there is a holiday on Monday, have the date formula start on the Tuesday, end on Friday, and the following week, start on a Monday again.

Mr Excel.xlsx
ABC
1# of fruits delivered (Beginning of the Week)# of fruits sold (Week Ending)fruits
2None sold
31/11/20211/15/2021Oranges
41/11/20211/15/2021Oranges
51/11/20211/15/2021Grapes
61/11/20211/15/2021Apples
71/19/20211/22/2021Apples
81/19/2021None sold
91/19/2021None sold
101/25/20211/29/2021Grapes
112/1/20212/5/2021Apples
122/1/20212/5/2021Oranges
132/8/20212/12/2021Apples
142/16/20212/19/2021Apples
152/16/20212/19/2021Grapes
162/16/2021None sold
172/16/20212/19/2021Apples
182/16/20212/19/2021Oranges
192/16/20212/19/2021Grapes
Data

Cell Formulas
RangeFormula
A2A2=DATE(I2,1,4)-WEEKDAY(DATE(I2,1,4),3)
B2B2=DATE(I2,1,4)-WEEKDAY(DATE(I2,1,4),2)+J2
C2:C8C2=COUNTIF(Data!A:A,[@[Beginning of Week (Mondays)]])
D2:D8D2=COUNTIF(Data!B:B,[@[Week Ending (Fridays)]])
F2:F8F2=COUNTIF(Data!B:B,[@[Week Ending (Fridays)]])
G2:G8G2=COUNTIF(Data!B:B,[@[Week Ending (Fridays)]])
H2:H8H2=COUNTIF(Data!B:B,[@[Week Ending (Fridays)]])
A3:B3,B4:B5,A6:B7,B8A3=A2+7
E3:E8E3=D3/C3
A4,A8A4=A3+8
A5A5=A4+6
Named Ranges
NameRefers ToCells
Data!_FilterDatabase=Data!\$A\$1:\$B\$19C2:C8
list=Data!\$A\$1:\$A\$19C2:C8
list1=Data!\$A:\$BC2:C8

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Replies
0
Views
62
Replies
2
Views
49
Replies
3
Views
245
Replies
1
Views
173
Replies
3
Views
119

1,130,400
Messages
5,641,927
Members
417,247
Latest member
Chitaah

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.

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

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