Dear All,

I'm sure I'll kick myself when I figure out how to get this to work...

I have a SS (excel 2003) sheet 1 is data imported from a database

On sheet 2 I am building a summary page. I am trying to get a simple
countif to tell me how many orders I have received on each day in April. I
have a list of all the days (01/04/2008 ... 30/04/2008) my count if says
=COUNTIF(Sheet1!B:B,Sheet2!A13). Column B on sheet 1 conatins the date of
the order and A13 contains the day on my summary page, this formula has been
dragged down for day in April.

The results are all 0, which is not right. I suspect the problem has
something to do with the data in Sheet1 column B containing the date and
time, not just the date. How can I fix this, bearing in mind that Sheet1
needs to keep refershing from the database.

TIA

Matthew

You could try it this way:

=SUMPRODUCT(--(INT(Sheet1!B$1:B$1000)=A13))

Note that you can't use full column references in SP with Excel prior
to 2007, so adjust the range to suit your data.

Hope this helps.

Pete

On Apr 9, 11:42*am, Matthew wrote:
Dear All,

I'm sure I'll kick myself when I figure out how to get this to work...

I have a SS (excel 2003) sheet 1 is data imported from a database

On sheet 2 I am building a summary page. *I am trying to get a simple
countif to tell me how many orders I have received on each day in April. *I
have a list of all the days (01/04/2008 ... 30/04/2008) my count if says
=COUNTIF(Sheet1!B:B,Sheet2!A13). *Column B on sheet 1 conatins the date of
the order and A13 contains the day on my summary page, this formula has been
dragged down for day in April.

The results are all 0, which is not right. *I suspect the problem has
something to do with the data in Sheet1 column B containing the date and
time, not just the date. *How can I fix this, bearing in mind that Sheet1
needs to keep refershing from the database.

TIA

Matthew

Hi Pete,

Thanks for getting back so quickly. With your formula I just get #VALUE in
every cell, I have tried fiddling with it but no luck. What does the -- bit
do?

Thanks

Matthew

"Pete_UK" wrote:

You could try it this way:

=SUMPRODUCT(--(INT(Sheet1!B$1:B$1000)=A13))

Note that you can't use full column references in SP with Excel prior
to 2007, so adjust the range to suit your data.

Hope this helps.

Pete

On Apr 9, 11:42 am, Matthew wrote:
Dear All,

I'm sure I'll kick myself when I figure out how to get this to work...

I have a SS (excel 2003) sheet 1 is data imported from a database

On sheet 2 I am building a summary page. I am trying to get a simple
countif to tell me how many orders I have received on each day in April. I
have a list of all the days (01/04/2008 ... 30/04/2008) my count if says
=COUNTIF(Sheet1!B:B,Sheet2!A13). Column B on sheet 1 conatins the date of
the order and A13 contains the day on my summary page, this formula has been
dragged down for day in April.

The results are all 0, which is not right. I suspect the problem has
something to do with the data in Sheet1 column B containing the date and
time, not just the date. How can I fix this, bearing in mind that Sheet1
needs to keep refershing from the database.

TIA

Matthew

Are you sure that the date/times in column B are in Excel format, and
not just text values that happen to look like date/times? Try
formatting them as yyyy/mm/dd to see if they change.

The double unary minus is used to coerce the logical values True and
False to 1 and 0, so they can be used arithmetically.

Hope this helps.

Pete



On Apr 9, 12:14*pm, Matthew wrote:
Hi Pete,

Thanks for getting back so quickly. *With your formula I just get #VALUE in
every cell, I have tried fiddling with it but no luck. *What does the -- bit
do?

Thanks

Matthew



"Pete_UK" wrote:
You could try it this way:

=SUMPRODUCT(--(INT(Sheet1!B$1:B$1000)=A13))

Note that you can't use full column references in SP with Excel prior
to 2007, so adjust the range to suit your data.

Hope this helps.

Pete

On Apr 9, 11:42 am, Matthew wrote:
Dear All,

I'm sure I'll kick myself when I figure out how to get this to work...

I have a SS (excel 2003) sheet 1 is data imported from a database

On sheet 2 I am building a summary page. *I am trying to get a simple
countif to tell me how many orders I have received on each day in April. *I
have a list of all the days (01/04/2008 ... 30/04/2008) my count if says
=COUNTIF(Sheet1!B:B,Sheet2!A13). *Column B on sheet 1 conatins the date of
the order and A13 contains the day on my summary page, this formula has been
dragged down for day in April.

The results are all 0, which is not right. *I suspect the problem has
something to do with the data in Sheet1 column B containing the date and
time, not just the date. *How can I fix this, bearing in mind that Sheet1
needs to keep refershing from the database.

TIA

Matthew- Hide quoted text -

- Show quoted text -

It's sort of working now...

I made a new column in Sheet1 (M) with int(b1) etc. filled down, which gives
me the date code for each cell. Now on sheet 2 when I use a version of your
=SUMPRODUCT(--(INT(Sheet1!B$1:B$1000)=A13)), without the INT bit it works
fine.

The only problem now is that the formula I dragged down on sheet1 doesn't
automatically calculate when I refresh the database data.

Thanks for your continued help



"Pete_UK" wrote:

Are you sure that the date/times in column B are in Excel format, and
not just text values that happen to look like date/times? Try
formatting them as yyyy/mm/dd to see if they change.

The double unary minus is used to coerce the logical values True and
False to 1 and 0, so they can be used arithmetically.

Hope this helps.

Pete



On Apr 9, 12:14 pm, Matthew wrote:
Hi Pete,

Thanks for getting back so quickly. With your formula I just get #VALUE in
every cell, I have tried fiddling with it but no luck. What does the -- bit
do?

Thanks

Matthew



"Pete_UK" wrote:
You could try it this way:

=SUMPRODUCT(--(INT(Sheet1!B$1:B$1000)=A13))

Note that you can't use full column references in SP with Excel prior
to 2007, so adjust the range to suit your data.

Hope this helps.

Pete

On Apr 9, 11:42 am, Matthew wrote:
Dear All,

I'm sure I'll kick myself when I figure out how to get this to work...

I have a SS (excel 2003) sheet 1 is data imported from a database

On sheet 2 I am building a summary page. I am trying to get a simple
countif to tell me how many orders I have received on each day in April. I
have a list of all the days (01/04/2008 ... 30/04/2008) my count if says
=COUNTIF(Sheet1!B:B,Sheet2!A13). Column B on sheet 1 conatins the date of
the order and A13 contains the day on my summary page, this formula has been
dragged down for day in April.

The results are all 0, which is not right. I suspect the problem has
something to do with the data in Sheet1 column B containing the date and
time, not just the date. How can I fix this, bearing in mind that Sheet1
needs to keep refershing from the database.

TIA

Matthew- Hide quoted text -

- Show quoted text -

Hi

You use an ODBC query? Then check 'Fill down formulas in adjacent columns'
in query properties.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Matthew" wrote in message
...
It's sort of working now...

I made a new column in Sheet1 (M) with int(b1) etc. filled down, which
gives
me the date code for each cell. Now on sheet 2 when I use a version of
your
=SUMPRODUCT(--(INT(Sheet1!B$1:B$1000)=A13)), without the INT bit it works
fine.

The only problem now is that the formula I dragged down on sheet1 doesn't
automatically calculate when I refresh the database data.

Thanks for your continued help



"Pete_UK" wrote:

Are you sure that the date/times in column B are in Excel format, and
not just text values that happen to look like date/times? Try
formatting them as yyyy/mm/dd to see if they change.

The double unary minus is used to coerce the logical values True and
False to 1 and 0, so they can be used arithmetically.

Hope this helps.

Pete



On Apr 9, 12:14 pm, Matthew wrote:
Hi Pete,

Thanks for getting back so quickly. With your formula I just get
#VALUE in
every cell, I have tried fiddling with it but no luck. What does
the -- bit
do?

Thanks

Matthew



"Pete_UK" wrote:
You could try it this way:

=SUMPRODUCT(--(INT(Sheet1!B$1:B$1000)=A13))

Note that you can't use full column references in SP with Excel prior
to 2007, so adjust the range to suit your data.

Hope this helps.

Pete

On Apr 9, 11:42 am, Matthew
wrote:
Dear All,

I'm sure I'll kick myself when I figure out how to get this to
work...

I have a SS (excel 2003) sheet 1 is data imported from a database

On sheet 2 I am building a summary page. I am trying to get a
simple
countif to tell me how many orders I have received on each day in
April. I
have a list of all the days (01/04/2008 ... 30/04/2008) my count if
says
=COUNTIF(Sheet1!B:B,Sheet2!A13). Column B on sheet 1 conatins the
date of
the order and A13 contains the day on my summary page, this formula
has been
dragged down for day in April.

The results are all 0, which is not right. I suspect the problem
has
something to do with the data in Sheet1 column B containing the
date and
time, not just the date. How can I fix this, bearing in mind that
Sheet1
needs to keep refershing from the database.

TIA

Matthew- Hide quoted text -

- Show quoted text -