I have tried a few methods but I am not getting the results I need. I am
trying to count how many products €“ any value (in column J) sold on a
particular date say 17/11/2008 (column H).

I used the following formula to count the number of times there is a value
in column L (for criteria of 12 mth) for anything with a date in column L
and got the results I expected, but when when i changed it to a date and
different range on same sheet I got no results.

=COUNTIFS($K$3:$K$398,"12 mth",$L$3:$L$398,"0") I got the number of times
I expected for this.

When I changed it to this formula I got no results, Why??
=COUNTIFS(H$3:H$398,"18/11/08",J$3:J$398,"0")

Excel is kind of picky when you refer to a date in a formula and there's
also the issue of date ambiguity.

Try one of these:

=COUNTIFS(H$3:H$398,""&DATE(2008,11,18),J$3:J$398 ,"0")

Use a cell to hold the date:

A1 = some date

=COUNTIFS(H$3:H$398,""&A1,J$3:J$398,"0")

As a last resort (I don't like this method!):

=COUNTIFS(H$3:H$398,""&--"2008/11/18",J$3:J$398,"0")


--
Biff
Microsoft Excel MVP


"kje.1953" wrote in message
...
I have tried a few methods but I am not getting the results I need. I am
trying to count how many products - any value (in column J) sold on a
particular date say 17/11/2008 (column H).

I used the following formula to count the number of times there is a value
in column L (for criteria of 12 mth) for anything with a date in column L
and got the results I expected, but when when i changed it to a date and
different range on same sheet I got no results.

=COUNTIFS($K$3:$K$398,"12 mth",$L$3:$L$398,"0") I got the number of
times
I expected for this.

When I changed it to this formula I got no results, Why??
=COUNTIFS(H$3:H$398,"18/11/08",J$3:J$398,"0")

Sumproduct would be the way to go i think, this example has the dates in
column B and the items in column A:

=SUMPRODUCT((B1:B20=(--("17/11/2008")))*(A1:A20<""))


--
The Code Cage Team

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=31883

COUNTIFS is faster (calculation-wise)

--
Biff
Microsoft Excel MVP


"The Code Cage Team" wrote in
message ...

Sumproduct would be the way to go i think, this example has the dates in
column B and the items in column A:

=SUMPRODUCT((B1:B20=(--("17/11/2008")))*(A1:A20<""))


--
The Code Cage Team

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
The Code Cage Team's Profile:
http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=31883

"T. Valko" wrote in message
...
Excel is kind of picky when you refer to a date in a formula and there's
also the issue of date ambiguity.

<snip

As a last resort (I don't like this method!):

=COUNTIFS(H$3:H$398,""&--"2008/11/18",J$3:J$398,"0")

You don't need the double unary and this ISO standard format is the best IMO

=COUNTIFS(H$3:H$398,"2008-11-18",J$3:J$398,"0")