I have the following sample sheet:

W/c 12/9 Total Loads Total Cases Failure Loads Failure Cases
12-Sep
13-Sep
14-Sep
15-Sep
16-Sep
TOTAL 0 0 0 0

The above is a 2nd sheet in a workbbook, with a 1st sheet containing the
core data from which I require Failure Cases above to be populated relevant
to the date.

The 1st sheet is as follows:
Column A = Week No.
Column B = Date (relevant to above sample sheet)
Column C = Load
Column D = Order No.
Column E = Customer
Column F = Haulier
Column G = Reason Code
Column H = Failure Cases (relevant to above sample sheet)

So far, I have come up with the following:
=SUMIF(Sheet2!$B$2:$B$9,"12-Sep",Sheet2!$H$2:$H$9), but this does require a
manual change against the dates of 13-15 Sep.

My question is as follows:-
(i) For the Friday, Saturday and Sunday dates (16-19 Sep) from Sheet 1, is
there any way I can get 16-Sep in my sample worksheet (Sheet 2) to show the
total cases for these 3 days.
(ii) Is there any way to automate the date within the formula.

If anyone has a better option, please let me know.

Ellie

Hi Ellie

One way

You could put your first date in say cell A1 and your second date in cell B1
and use the following
=SUMPRODUCT(--(Sheet2!$B$2:$B$9=$A$1),--(Sheet2!$B$2:$B$9<=$B$1),Sheet2!$H$2:$H$9)

Change the A1 and B1 to whatever Sheet and cell ranges you use.
If the data is for a single day, then make A1 and B1 the same date

Regards

Roger Govier


Ellie wrote:
I have the following sample sheet:

W/c 12/9 Total Loads Total Cases Failure Loads Failure Cases
12-Sep
13-Sep
14-Sep
15-Sep
16-Sep
TOTAL 0 0 0 0

The above is a 2nd sheet in a workbbook, with a 1st sheet containing the
core data from which I require Failure Cases above to be populated relevant
to the date.

The 1st sheet is as follows:
Column A = Week No.
Column B = Date (relevant to above sample sheet)
Column C = Load
Column D = Order No.
Column E = Customer
Column F = Haulier
Column G = Reason Code
Column H = Failure Cases (relevant to above sample sheet)

So far, I have come up with the following:
=SUMIF(Sheet2!$B$2:$B$9,"12-Sep",Sheet2!$H$2:$H$9), but this does require a
manual change against the dates of 13-15 Sep.

My question is as follows:-
(i) For the Friday, Saturday and Sunday dates (16-19 Sep) from Sheet 1, is
there any way I can get 16-Sep in my sample worksheet (Sheet 2) to show the
total cases for these 3 days.
(ii) Is there any way to automate the date within the formula.

If anyone has a better option, please let me know.

Ellie

Think Ellie found your response helpful and
wanted to say: "Thanks, Roger ! " <g
(she expressed the first part in the web/CDO interface)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Thanks Max (and Ellie).
I was just wondering whether Ellie had a further problem, but I guessed if
she had, she would have been looking for a response and seen her blank
message and posted again.

Regards

Roger Govier


Max wrote:
Think Ellie found your response helpful and
wanted to say: "Thanks, Roger ! " <g
(she expressed the first part in the web/CDO interface)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--