Date and Countif Week Formula

searchingforhelp

New Member
Joined
Nov 11, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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.

Thank you in advance for your help.

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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Watch MrExcel Video

Forum statistics

Threads
1,130,179
Messages
5,640,632
Members
417,159
Latest member
Mayozero

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
Top