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

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

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

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()))

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

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.