Thread
:
counting formula
View Single Post
#
5
Posted to microsoft.public.excel.misc
Sandy Mann
external usenet poster
Posts: 2,345
counting formula
Or you could combine both your formulas:
=SUMPRODUCT(--(A1:A100=1),(--(LEN(B1:B100)<LEN(SUBSTITUTE(B1:B100,"Dave",""))) ))
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
Replace @mailinator.com with @tiscali.co.uk
"Gary''s Student" wrote in message
...
You can use a helper column. In Z1, enter:
=--(LEN(B1)<LEN(SUBSTITUTE(B1,"Dave",""))) and copy down and then use:
=SUMPRODUCT(--(A1:A100=C5),--(Z1:Z100))
--
Gary''s Student - gsnu200773
"Tendresse" wrote:
i'm using the following formula to count the number of times a certain
value
appears in column A when the value in column B=Dave. I want to adjust
this
formula so that it counts the number of times a certain value appears in
column A when the value in column B 'contains' the string Dave. Cells in
column B may contain something like Dave Smith (not just Dave).
=SUMPRODUCT(--(A1:A100=C5),--(B1:B100="Dave"))
Thanks in advance.
Excel 2003.
Reply With Quote
Sandy Mann
View Public Profile
Find all posts by Sandy Mann