Home |
Search |
Today's Posts |
#1
|
|||
|
|||
LOOKUP and COUNTIF
hi all,
i wonder if anyone can help me with a formula i want to create. in column A i have names of products, and in column B i have the date (sell by date, for example). how can i create a formula to count the number of a specific product that has a data equal or above the current date (or a specific date)? e.g. count the number of bananas that have a date greater or equal than todays's date (18/01/05) = 2 A B Bananas 01/04/05 Apples 05/06/05 Apples 28/02/05 Oranges 04/03/05 Bananas 14/01/05 Bananas 22/02/05 Apples 16/01/05 ps: these dates are in uk format - dd/mm/yy !! thanks to anyone who can help |
#2
|
|||
|
|||
One way:
=SUMPRODUCT(--(A1:A100="Bananas"), --(B1:B100=TODAY())) for an explanation of "--" see http://www.mcgimpsey.com/excel/doubleneg.html In article , borris wrote: hi all, i wonder if anyone can help me with a formula i want to create. in column A i have names of products, and in column B i have the date (sell by date, for example). how can i create a formula to count the number of a specific product that has a data equal or above the current date (or a specific date)? e.g. count the number of bananas that have a date greater or equal than todays's date (18/01/05) = 2 A B Bananas 01/04/05 Apples 05/06/05 Apples 28/02/05 Oranges 04/03/05 Bananas 14/01/05 Bananas 22/02/05 Apples 16/01/05 ps: these dates are in uk format - dd/mm/yy !! thanks to anyone who can help |
#3
|
|||
|
|||
If you do want to use an actual date in the formula, I believe that you have
to use DATEVALUE: .....--(B1:B100=DATEVALUE("14/01/05")) "JE McGimpsey" wrote: One way: =SUMPRODUCT(--(A1:A100="Bananas"), --(B1:B100=TODAY())) for an explanation of "--" see http://www.mcgimpsey.com/excel/doubleneg.html In article , borris wrote: hi all, i wonder if anyone can help me with a formula i want to create. in column A i have names of products, and in column B i have the date (sell by date, for example). how can i create a formula to count the number of a specific product that has a data equal or above the current date (or a specific date)? e.g. count the number of bananas that have a date greater or equal than todays's date (18/01/05) = 2 A B Bananas 01/04/05 Apples 05/06/05 Apples 28/02/05 Oranges 04/03/05 Bananas 14/01/05 Bananas 22/02/05 Apples 16/01/05 ps: these dates are in uk format - dd/mm/yy !! thanks to anyone who can help |
#4
|
|||
|
|||
Did you try it?
In article , Ken wrote: If you do want to use an actual date in the formula, I believe that you have to use DATEVALUE: ....--(B1:B100=DATEVALUE("14/01/05")) "JE McGimpsey" wrote: One way: =SUMPRODUCT(--(A1:A100="Bananas"), --(B1:B100=TODAY())) |
#5
|
|||
|
|||
JE McGimpsey wrote in
: One way: =SUMPRODUCT(--(A1:A100="Bananas"), --(B1:B100=TODAY())) hello, yes thank you for this. it worked. however i did experience some problems but i was managed to work around them. one is that i get an #NUM! error if i use the column as the range e.g A:A. however i can work around this by just using something like A1:A2000 to cover everything. also if i specify to look for dates earlier than todays date it seems to count blank cells as well (ones that have no date entered). again i was able to work around this in the end. thanks for your help |
#6
|
|||
|
|||
Array functions (which SUMPRODUCT is, even if you don't have to use
CTRL-SHIFT-ENTER) cannot operate on entire columns. In article , borris wrote: one is that i get an #NUM! error if i use the column as the range e.g A:A. however i can work around this by just using something like A1:A2000 to cover everything. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|