I am tring to count the number of time a specific text appears in a range.
The following formula counts the total number of entries in my range but is
not seperating the specific text I want. Can anyone help with this?

=SUMPRODUCT(--(Start_Rep:Finish_Rep=B4)--(Start:Finish="*Utility - *"))

Thanks
Peter

You've added the two booleans (with the --), rather than allowing SUMPRODUCT
to multiply them.
I guess that instead of
=SUMPRODUCT(--(Start_Rep:Finish_Rep=B4)--(Start:Finish="*Utility - *"))
you may have intended
=SUMPRODUCT(--(Start_Rep:Finish_Rep=B4),--(Start:Finish="*Utility - *")) or
=SUMPRODUCT((Start_Rep:Finish_Rep=B4)*(Start:Finis h="*Utility - *"))
--
David Biddulph

"Looping through" wrote in
message ...
I am tring to count the number of time a specific text appears in a range.
The following formula counts the total number of entries in my range but
is
not seperating the specific text I want. Can anyone help with this?

=SUMPRODUCT(--(Start_Rep:Finish_Rep=B4)--(Start:Finish="*Utility - *"))

Thanks
Peter

Or if you're looking for "Utility - " in that range and it could be buried in
with other text:

=SUMPRODUCT(--(start_rep:Finish_rep=B4),
--ISNUMBER(SEARCH("Utility - ",start:finish)))


=search() is not case sensitive.
=find() is case sensitive.



Looping through wrote:

I am tring to count the number of time a specific text appears in a range.
The following formula counts the total number of entries in my range but is
not seperating the specific text I want. Can anyone help with this?

=SUMPRODUCT(--(Start_Rep:Finish_Rep=B4)--(Start:Finish="*Utility - *"))

Thanks
Peter

--

Dave Peterson

David, Thanks for your suggestion. However, both formulas you offered return
a "0" when in fact the correct answer is 3. I think the TEXT this formula is
looking for is the problem. "Utility - " is just the begining of the
information in the cell. A typical entry would say Utility - XYZ Distribitor.

Any suggestions.
Peter

"David Biddulph" wrote:

You've added the two booleans (with the --), rather than allowing SUMPRODUCT
to multiply them.
I guess that instead of
=SUMPRODUCT(--(Start_Rep:Finish_Rep=B4)--(Start:Finish="*Utility - *"))
you may have intended
=SUMPRODUCT(--(Start_Rep:Finish_Rep=B4),--(Start:Finish="*Utility - *")) or
=SUMPRODUCT((Start_Rep:Finish_Rep=B4)*(Start:Finis h="*Utility - *"))
--
David Biddulph

"Looping through" wrote in
message ...
I am tring to count the number of time a specific text appears in a range.
The following formula counts the total number of entries in my range but
is
not seperating the specific text I want. Can anyone help with this?

=SUMPRODUCT(--(Start_Rep:Finish_Rep=B4)--(Start:Finish="*Utility - *"))

Thanks
Peter

Hi,

I belive you cannot use a wild card search with Sumproduct.

Try
=SUMPRODUCT((ISNUMBER(SEARCH("Utility",Start:Finis h)))*(Start_Rep:Finish_Rep=B4)) i.e if you want to ensure it meets the criterion if not

=SUMPRODUCT((ISNUMBER(SEARCH("Utility",Start:Finis h)))--(Start_Rep:Finish_Rep=B4)) if you want only want 1 criteria.

"David Biddulph" wrote:

You've added the two booleans (with the --), rather than allowing SUMPRODUCT
to multiply them.
I guess that instead of
=SUMPRODUCT(--(Start_Rep:Finish_Rep=B4)--(Start:Finish="*Utility - *"))
you may have intended
=SUMPRODUCT(--(Start_Rep:Finish_Rep=B4),--(Start:Finish="*Utility - *")) or
=SUMPRODUCT((Start_Rep:Finish_Rep=B4)*(Start:Finis h="*Utility - *"))
--
David Biddulph

"Looping through" wrote in
message ...
I am tring to count the number of time a specific text appears in a range.
The following formula counts the total number of entries in my range but
is
not seperating the specific text I want. Can anyone help with this?

=SUMPRODUCT(--(Start_Rep:Finish_Rep=B4)--(Start:Finish="*Utility - *"))

Thanks
Peter