countif
 

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

countif
 

Use this:

=SUMPRODUCT((A1:A100=start_date)*(A1:A100<=end_da te)*
(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

On Aug 28, 3:41*pm, alistew wrote:
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

countif
 

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="Del ivered"))

Andrea Jones
www.allaboutclait.com


"alistew" wrote:

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

countif
 

thanks very much this works perfectly!

"Pete_UK" wrote:

Use this:

=SUMPRODUCT((A1:A100=start_date)*(A1:A100<=end_da te)*
(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

On Aug 28, 3:41 pm, alistew wrote:
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

countif
 

thank you very much for your response

alison

"Andrea Jones" wrote:

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="Del ivered"))

Andrea Jones
www.allaboutclait.com


"alistew" wrote:

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

countif
 

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

Pete

On Aug 28, 4:25*pm, alistew wrote:
thanks very much this works perfectly!



"Pete_UK" wrote:
Use this:

=SUMPRODUCT((A1:A100=start_date)*(A1:A100<=end_da te)*
(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

On Aug 28, 3:41 pm, alistew wrote:
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- Hide quoted text -

- Show quoted text -