Countifs -Count no of times products have been sold on particular
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") |
Countifs -Count no of times products have been sold on particular
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") |
Countifs -Count no of times products have been sold on particular
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 -Count no of times products have been sold on particular
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 |
Countifs -Count no of times products have been sold on particular
"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") |
All times are GMT +1. The time now is 02:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com