countif

  • Thread starter Thread starter alistew
  • Start date Start date
A

alistew

Hi

i have a worksheet with 2 columns of data. column a has dates and column b
has text (delivered or not delivered). i want to count the number of
instances of the word delivered between a certain criteria of dates. ie
01/01/09 and 31/01/09. The dates are in a start date and end date cell and
will change.

Appreciate any help.

Regards
ali
 
Use this:

=SUMPRODUCT((A1:A100>=start_date)*(A1:A100<=end_date)*
(B1:B100="Delivered"))

This assumes your dates are inclusive - change >= to > and/or <= to <
if this is not the case.

Adjust your ranges to suit - I've assumed 100 rows of data.

Hope this helps.

Pete
 
Assuming your start date is in E1 and your end date in F1 and the data is in
cells A2 to B50 you could use:

=SUMPRODUCT((A2:A50>=E1)*(A2:A50<=F1)*(B2:B50="Delivered"))

Andrea Jones
www.allaboutclait.com
 
thanks very much this works perfectly!

Pete_UK said:
Use this:

=SUMPRODUCT((A1:A100>=start_date)*(A1:A100<=end_date)*
(B1:B100="Delivered"))

This assumes your dates are inclusive - change >= to > and/or <= to <
if this is not the case.

Adjust your ranges to suit - I've assumed 100 rows of data.

Hope this helps.

Pete
Click to expand...
 
thank you very much for your response

alison

Andrea Jones said:
Assuming your start date is in E1 and your end date in F1 and the data is in
cells A2 to B50 you could use:

=SUMPRODUCT((A2:A50>=E1)*(A2:A50<=F1)*(B2:B50="Delivered"))

Andrea Jones
www.allaboutclait.com
Click to expand...
 
Back
Top