Sumproduct or countif or both...

  • Thread starter Thread starter peterpeter
  • Start date Start date
P

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)))
 
You were close. Try this:

=SUMPRODUCT(--(A16:A3851>=B2),--(A16:A3851<=B3),--(C16:C3851="no show"))
 
Simply elegant, thankyou very much, worked a treat.
and you were too kind, i was way off hehe :)

T. Valko said:
You were close. Try this:

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


--
Biff
Microsoft Excel MVP


peterpeter said:
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)))
Click to expand...
Click to expand...
 
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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

T. Valko said:
You were close. Try this:

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


--
Biff
Microsoft Excel MVP


peterpeter said:
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)))
Click to expand...
Click to expand...
Click to expand...
 
Back
Top