Simple COUNTIF problem

[Matthew]

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

 

[Pete_UK]

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

 

[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]

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

 

[Matthew]

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

 

[Arvi Laanemets]

Hi

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