Remember Me?

#1
September 15th 05, 10:59 AM
 Ellie Posts: n/a
COUNTIF or not to COUNTIF on a range in another sheet

I have a report over 2 separate sheets in a workbook.

On the first sheet I have my core data, covering approx. 8 columns,
inclusive of date, load number (in columns B and C) and cases (in column H).
On the second sheet I am currently manually inputting the result of data
entered into the first sheet.

The data in columns B, C and H are what I manually input into the second
sheet.

In this 2nd sheet I have column A with the date in the same format as on the
first worksheet along with column D being the total number of loads for that
date and column E being the total number of cases also for that date.
Unfortunately on the first worksheet I sometimes have dates where no data is
available and put this in as "No Issues", but having used COUNTIF against the
date find that No Issues is being counted, as I obviously have entered the
date into the first sheet.

e.g. of 2nd sheet in workbook:-
12-Sep
13-Sep
14-Sep
15-Sep
16-Sep
TOTAL 0 0 0 0

As a sample, I had used =COUNTIF(Sheet1!B2:I6,"=12-Sep") against the Failure
Loads for 12-Sep, which returns the correct number, but unfortunately when
going for 13-Sep returned 1, even though it was No Issues, so should be as a
0.

Is there any suggestion to amend the above COUNTIF calculation, to give me
what I require, or something else to do this for me.

Many thanks.

Ellie

#2
September 15th 05, 02:33 PM
 Paul Sheppard Posts: n/a

Ellie Wrote:
I have a report over 2 separate sheets in a workbook.

On the first sheet I have my core data, covering approx. 8 columns,
inclusive of date, load number (in columns B and C) and cases (in
column H).
On the second sheet I am currently manually inputting the result of
data
entered into the first sheet.

The data in columns B, C and H are what I manually input into the
second
sheet.

In this 2nd sheet I have column A with the date in the same format as
on the
first worksheet along with column D being the total number of loads for
that
date and column E being the total number of cases also for that date.
Unfortunately on the first worksheet I sometimes have dates where no
data is
available and put this in as "No Issues", but having used COUNTIF
against the
date find that No Issues is being counted, as I obviously have entered
the
date into the first sheet.

e.g. of 2nd sheet in workbook:-
12-Sep
13-Sep
14-Sep
15-Sep
16-Sep
TOTAL 0 0 0
0

As a sample, I had used =COUNTIF(Sheet1!B2:I6,"=12-Sep") against the
Failure
Loads for 12-Sep, which returns the correct number, but unfortunately
when
going for 13-Sep returned 1, even though it was No Issues, so should be
as a
0.

Is there any suggestion to amend the above COUNTIF calculation, to give
me
what I require, or something else to do this for me.

Many thanks.

Ellie

Hi Ellie

Your formula looks ok except you don't need = infront 0f 12-Sep

To help further put a sample of the data from the first sheet

As an alternative, you could just put subtotals on your Data
Subtotals at each change of date

--
Paul Sheppard

------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783

#3
September 15th 05, 03:21 PM
 Ellie Posts: n/a

Hi Paul

Week 37 12-Sep 81022 54171503 Asda Normanton WRW 14 2560 Failed to be
collected by Asda backhaul. To be rebooked.
12-Sep 81023 54171505 Asda Normanton WRW 14 2080 Failed to be collected by
Asda backhaul. To be rebooked.
12-Sep 80986 54171508/09 Asda Washington WRW 5 2254 Sub-contractor
collected the wrong load. To be rebooked.
12-Sep HAL 62106 85201260/63/80 Asda Grangemouth HAL 2 3481 Booking time
23:00hrs. Delayed en route. To be rebooked.
12-Sep 80985 54171538/39/40/41 Asda Lutterworth WRW 12 4914 Booking time
22:00hrs. To be rebooked.
13-Sep NO ISSUES
14-Sep HAL 62118 85201363/71/76 Asda Wigan HAL 6 2836 Vehicle broke down.
Rebooked 16/09/05 @ 19:20hrs.

The above is a sample of the first sheet mentioned below. Hope this helps.

Thanks for letting me know about the = with the date. Wasn't sure if it was
necessary or not.

Ellie

"Paul Sheppard" wrote:

Ellie Wrote:
I have a report over 2 separate sheets in a workbook.

On the first sheet I have my core data, covering approx. 8 columns,
inclusive of date, load number (in columns B and C) and cases (in
column H).
On the second sheet I am currently manually inputting the result of
data
entered into the first sheet.

The data in columns B, C and H are what I manually input into the
second
sheet.

In this 2nd sheet I have column A with the date in the same format as
on the
first worksheet along with column D being the total number of loads for
that
date and column E being the total number of cases also for that date.
Unfortunately on the first worksheet I sometimes have dates where no
data is
available and put this in as "No Issues", but having used COUNTIF
against the
date find that No Issues is being counted, as I obviously have entered
the
date into the first sheet.

e.g. of 2nd sheet in workbook:-
12-Sep
13-Sep
14-Sep
15-Sep
16-Sep
TOTAL 0 0 0
0

As a sample, I had used =COUNTIF(Sheet1!B2:I6,"=12-Sep") against the
Failure
Loads for 12-Sep, which returns the correct number, but unfortunately
when
going for 13-Sep returned 1, even though it was No Issues, so should be
as a
0.

Is there any suggestion to amend the above COUNTIF calculation, to give
me
what I require, or something else to do this for me.

Many thanks.

Ellie

Hi Ellie

Your formula looks ok except you don't need = infront 0f 12-Sep

To help further put a sample of the data from the first sheet

As an alternative, you could just put subtotals on your Data
Subtotals at each change of date

--
Paul Sheppard

------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783

#4
September 15th 05, 03:26 PM
 Ellie Posts: n/a

Sorry, also forgot to mention that for the 5th day on the 2nd sheet (16-Sep),
this incorporates the Friday, Saturday and Sunday dates (16-18 Sep) from the
1st sheet.

Ellie

"Paul Sheppard" wrote:

Ellie Wrote:
I have a report over 2 separate sheets in a workbook.

On the first sheet I have my core data, covering approx. 8 columns,
inclusive of date, load number (in columns B and C) and cases (in
column H).
On the second sheet I am currently manually inputting the result of
data
entered into the first sheet.

The data in columns B, C and H are what I manually input into the
second
sheet.

In this 2nd sheet I have column A with the date in the same format as
on the
first worksheet along with column D being the total number of loads for
that
date and column E being the total number of cases also for that date.
Unfortunately on the first worksheet I sometimes have dates where no
data is
available and put this in as "No Issues", but having used COUNTIF
against the
date find that No Issues is being counted, as I obviously have entered
the
date into the first sheet.

e.g. of 2nd sheet in workbook:-
12-Sep
13-Sep
14-Sep
15-Sep
16-Sep
TOTAL 0 0 0
0

As a sample, I had used =COUNTIF(Sheet1!B2:I6,"=12-Sep") against the
Failure
Loads for 12-Sep, which returns the correct number, but unfortunately
when
going for 13-Sep returned 1, even though it was No Issues, so should be
as a
0.

Is there any suggestion to amend the above COUNTIF calculation, to give
me
what I require, or something else to do this for me.

Many thanks.

Ellie

Hi Ellie

Your formula looks ok except you don't need = infront 0f 12-Sep

To help further put a sample of the data from the first sheet

As an alternative, you could just put subtotals on your Data
Subtotals at each change of date

--
Paul Sheppard

------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783

#5
September 15th 05, 10:06 PM
 Paul Sheppard Posts: n/a

Hi Ellie

To get over the problem of No Issues on the 13th instead of COUNTIF use

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

This needs to be enterred as an array, so type in or copy/paste the
formula and instead of pressing Enter press CTRL/SHIFT/ENTER all
together
This will make the formula look like this

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

This is looking at the date column B and the load column C, I assume
you put No issues in the comments so column C for the 13th will be
blank, and counts instances of a date so long as the load column is
blank

Haven't had time to work out the weekend issue, but I'm sure someone on
here will help

Have attached a zip copy of the file I worked on. Sorry am going on
holiday tomorrow, not back till 2nd October, if you haven't sorted it
by then e-mail me

+-------------------------------------------------------------------+
|Filename: Asda.zip |
http://www.excelforum.com/attachment.php?postid=3826 |
+-------------------------------------------------------------------+

--
Paul Sheppard

------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Andy Excel Discussion (Misc queries) 2 September 7th 05 03:19 PM TinaMo Excel Worksheet Functions 3 June 3rd 05 10:56 PM Renee - California Excel Worksheet Functions 5 May 27th 05 07:09 PM mbparks Excel Worksheet Functions 3 January 2nd 05 11:06 PM JK Excel Worksheet Functions 3 November 3rd 04 07:50 AM

All times are GMT +1. The time now is 04:25 AM.