Formula array adaptation
 

I am currently using a formula array, kindly provided by Paul Sheppard, as
follows:-

{=SUM((Sheet1!$B$2:$B$9=Sheet2!A2)*(Sheet1!$C$2:$C $9<""))}

This array basically provides me with the number of entries with a specific
date in Column B and then how many actually include something other than no
issues in Column C from one sheet in a workbook and populates the appropriate
cell in another sheet in the same workbook.

As the 2nd sheet is only permitted to show 5 dates, albeit the 5th date of
each week (example below shows 16/9) is to include the Saturday and Sunday
dates (17&18/9) within it, could anyone help me in being able to adapt the
above array to encapsulate the additional Saturday and Sunday data under the
Friday data.

e.g. of 2nd sheet as below:

W/C 12/09 Total Loads Total Cases Failure Loads Failure Cases
12-Sep
13-Sep
14-Sep
15-Sep
16-Sep
TOTAL 0 0 0 0

Many thanks.

Ellie

try
{=SUM((Sheet1!$B$2:$B$9=Sheet2!A2)*(Sheet1!$C$2:$C $9<""))+if(weekday(sheet2!A2)=5,SUM((Sheet1!$B$2: $B$9=Sheet2!A2+1)*(Sheet1!$C$2:$C$9<""))+SUM((She et1!$B$2:$B$9=Sheet2!A2+2)*(Sheet1!$C$2:$C$9<"")) ,0)}

"Ellie" wrote:

I am currently using a formula array, kindly provided by Paul Sheppard, as
follows:-

{=SUM((Sheet1!$B$2:$B$9=Sheet2!A2)*(Sheet1!$C$2:$C $9<""))}

This array basically provides me with the number of entries with a specific
date in Column B and then how many actually include something other than no
issues in Column C from one sheet in a workbook and populates the appropriate
cell in another sheet in the same workbook.

As the 2nd sheet is only permitted to show 5 dates, albeit the 5th date of
each week (example below shows 16/9) is to include the Saturday and Sunday
dates (17&18/9) within it, could anyone help me in being able to adapt the
above array to encapsulate the additional Saturday and Sunday data under the
Friday data.

e.g. of 2nd sheet as below:

W/C 12/09 Total Loads Total Cases Failure Loads Failure Cases
12-Sep
13-Sep
14-Sep
15-Sep
16-Sep
TOTAL 0 0 0 0

Many thanks.

Ellie

Thanks bj. but unfortunately having entered this formula array, when testing
by changing sheet 1 17/9 from no issues to an issue, this showed up on sheet
2 under 15/9.

Any help would be appreciated, but have double and triple checked my entries
and is as you have below.

Ellie

"bj" wrote:

try
{=SUM((Sheet1!$B$2:$B$9=Sheet2!A2)*(Sheet1!$C$2:$C $9<""))+if(weekday(sheet2!A2)=5,SUM((Sheet1!$B$2: $B$9=Sheet2!A2+1)*(Sheet1!$C$2:$C$9<""))+SUM((She et1!$B$2:$B$9=Sheet2!A2+2)*(Sheet1!$C$2:$C$9<"")) ,0)}

"Ellie" wrote:

I am currently using a formula array, kindly provided by Paul Sheppard, as
follows:-

{=SUM((Sheet1!$B$2:$B$9=Sheet2!A2)*(Sheet1!$C$2:$C $9<""))}

This array basically provides me with the number of entries with a specific
date in Column B and then how many actually include something other than no
issues in Column C from one sheet in a workbook and populates the appropriate
cell in another sheet in the same workbook.

As the 2nd sheet is only permitted to show 5 dates, albeit the 5th date of
each week (example below shows 16/9) is to include the Saturday and Sunday
dates (17&18/9) within it, could anyone help me in being able to adapt the
above array to encapsulate the additional Saturday and Sunday data under the
Friday data.

e.g. of 2nd sheet as below:

W/C 12/09 Total Loads Total Cases Failure Loads Failure Cases
12-Sep
13-Sep
14-Sep
15-Sep
16-Sep
TOTAL 0 0 0 0

Many thanks.

Ellie

that is because I screwed up
change the
<""))+if(weekday(sheet2!A2)=5,SUM
to
<""))+if(weekday(sheet2!A2)=6,SUM

Friday is 6 not 5 in Weekday


"Ellie" wrote:

Thanks bj. but unfortunately having entered this formula array, when testing
by changing sheet 1 17/9 from no issues to an issue, this showed up on sheet
2 under 15/9.

Any help would be appreciated, but have double and triple checked my entries
and is as you have below.

Ellie

"bj" wrote:

try
{=SUM((Sheet1!$B$2:$B$9=Sheet2!A2)*(Sheet1!$C$2:$C $9<""))+if(weekday(sheet2!A2)=5,SUM((Sheet1!$B$2: $B$9=Sheet2!A2+1)*(Sheet1!$C$2:$C$9<""))+SUM((She et1!$B$2:$B$9=Sheet2!A2+2)*(Sheet1!$C$2:$C$9<"")) ,0)}

"Ellie" wrote:

I am currently using a formula array, kindly provided by Paul Sheppard, as
follows:-

{=SUM((Sheet1!$B$2:$B$9=Sheet2!A2)*(Sheet1!$C$2:$C $9<""))}

This array basically provides me with the number of entries with a specific
date in Column B and then how many actually include something other than no
issues in Column C from one sheet in a workbook and populates the appropriate
cell in another sheet in the same workbook.

As the 2nd sheet is only permitted to show 5 dates, albeit the 5th date of
each week (example below shows 16/9) is to include the Saturday and Sunday
dates (17&18/9) within it, could anyone help me in being able to adapt the
above array to encapsulate the additional Saturday and Sunday data under the
Friday data.

e.g. of 2nd sheet as below:

W/C 12/09 Total Loads Total Cases Failure Loads Failure Cases
12-Sep
13-Sep
14-Sep
15-Sep
16-Sep
TOTAL 0 0 0 0

Many thanks.

Ellie