Sumproduct or countif or both...
 

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

Sumproduct or countif or both...
 

You were close. Try this:

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


--
Biff
Microsoft Excel MVP


"peterpeter" wrote in message
...
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)))

Sumproduct or countif or both...
 

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

"T. Valko" wrote:

You were close. Try this:

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


--
Biff
Microsoft Excel MVP


"peterpeter" wrote in message
...
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)))

Sumproduct or countif or both...
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"peterpeter" wrote in message
...
Simply elegant, thankyou very much, worked a treat.
and you were too kind, i was way off hehe :)

"T. Valko" wrote:

You were close. Try this:

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


--
Biff
Microsoft Excel MVP


"peterpeter" wrote in message
...
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)))