LOOKUP and COUNTIF

[borris]

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

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(A1:A100="Bananas"), --(B1:B100>=TODAY()))


for an explanation of "--" see

http://www.mcgimpsey.com/excel/doubleneg.html

 

[Ken]

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]

Did you try it?

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

 

[borris]

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

 

[JE McGimpsey]

Array functions (which SUMPRODUCT is, even if you don't have to use
CTRL-SHIFT-ENTER) cannot operate on entire columns.