countif

[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

 

[Pete_UK]

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

 

[Andrea Jones]

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

 

[alistew]

thanks very much this works perfectly!

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

 

[alistew]

thank you very much for your response

alison

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

 

[Pete_UK]

Glad to hear it, Ali - thanks for feeding back.

Pete