Sumproduct or countif or both...

[peterpeter]

I want to count the no of times text [No Show]appears n column C, within a
date range, where the dates are in column A & start & end dates can be
entered into cells B2 & B3 respectively. rows used for dates & data are
16:3851
Tried countif & sumproduct and have come to the realisation i need specific
help.
the following is the last iteration of where i was going...probably way off,
any help would be appreciated...

=SUMPRODUCT((MATCH(C16:C3851,"No Show",0))*((A16:A3851>=B2)*(A16:A3851<=B3)))

 

[T. Valko]

You were close. Try this:

=SUMPRODUCT(--(A16:A3851>=B2),--(A16:A3851<=B3),--(C16:C3851="no show"))

 

[peterpeter]

Simply elegant, thankyou very much, worked a treat.
and you were too kind, i was way off hehe

You were close. Try this:

=SUMPRODUCT(--(A16:A3851>=B2),--(A16:A3851<=B3),--(C16:C3851="no show"))


--
Biff
Microsoft Excel MVP

I want to count the no of times text [No Show]appears n column C, within a
date range, where the dates are in column A & start & end dates can be
entered into cells B2 & B3 respectively. rows used for dates & data are
16:3851
Tried countif & sumproduct and have come to the realisation i need
specific
help.
the following is the last iteration of where i was going...probably way
off,
any help would be appreciated...

=SUMPRODUCT((MATCH(C16:C3851,"No
Show",0))*((A16:A3851>=B2)*(A16:A3851<=B3)))

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Simply elegant, thankyou very much, worked a treat.
and you were too kind, i was way off hehe

You were close. Try this:

=SUMPRODUCT(--(A16:A3851>=B2),--(A16:A3851<=B3),--(C16:C3851="no show"))


--
Biff
Microsoft Excel MVP

I want to count the no of times text [No Show]appears n column C, within
a
date range, where the dates are in column A & start & end dates can be
entered into cells B2 & B3 respectively. rows used for dates & data are
16:3851
Tried countif & sumproduct and have come to the realisation i need
specific
help.
the following is the last iteration of where i was going...probably way
off,
any help would be appreciated...

=SUMPRODUCT((MATCH(C16:C3851,"No
Show",0))*((A16:A3851>=B2)*(A16:A3851<=B3)))