Help!!!

[Ellie]

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

 

[Roger Govier]

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

 

[Max]

Think Ellie found your response helpful and
wanted to say: "Thanks, Roger ! " <g>
(she expressed the first part in the web/CDO interface)

 

[Roger Govier]

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