counting formula
 

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.

counting formula
 

left(b1:b100,4)="dave"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

counting formula
 

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.

counting formula
 

Say you put the first name in a particular cell, say D5, then try this:

=SUMPRODUCT((A1:A100=C5)*(ISNUMBER(FIND(D5,B1:B100 ))))
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


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

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.

counting formula
 

To make the formula more robust, since the Find() function is case
sensitive, you can use the Search() function instead:

=SUMPRODUCT((A1:A100=C5)*(ISNUMBER(SEARCH(D5,B1:B1 00))))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"RagDyeR" wrote in message
...
Say you put the first name in a particular cell, say D5, then try this:

=SUMPRODUCT((A1:A100=C5)*(ISNUMBER(FIND(D5,B1:B100 ))))
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


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

counting formula
 

Thank you guys for all your help ... muchly appreciated.
I used the formula below and it worked brilliantly.

Don, your formula assumes the word Dave is always at the beginning of the
cell. That's my mistake, i didn't mention in my question that there could be
entries like 'From Dave'. But thank you very much, i'm sure it will come a
situation where i'll need to apply the LEFT function. Now i have the answer
in advance.

Thanks again ... Tendresse

"RagDyeR" wrote:

To make the formula more robust, since the Find() function is case
sensitive, you can use the Search() function instead:

=SUMPRODUCT((A1:A100=C5)*(ISNUMBER(SEARCH(D5,B1:B1 00))))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"RagDyeR" wrote in message
...
Say you put the first name in a particular cell, say D5, then try this:

=SUMPRODUCT((A1:A100=C5)*(ISNUMBER(FIND(D5,B1:B100 ))))
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


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

counting formula
 

And we all appreciate your feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tendresse" wrote in message
...
Thank you guys for all your help ... muchly appreciated.
I used the formula below and it worked brilliantly.

Don, your formula assumes the word Dave is always at the beginning of the
cell. That's my mistake, i didn't mention in my question that there could
be
entries like 'From Dave'. But thank you very much, i'm sure it will come a
situation where i'll need to apply the LEFT function. Now i have the
answer
in advance.

Thanks again ... Tendresse

"RagDyeR" wrote:

To make the formula more robust, since the Find() function is case
sensitive, you can use the Search() function instead:

=SUMPRODUCT((A1:A100=C5)*(ISNUMBER(SEARCH(D5,B1:B1 00))))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"RagDyeR" wrote in message
...
Say you put the first name in a particular cell, say D5, then try this:

=SUMPRODUCT((A1:A100=C5)*(ISNUMBER(FIND(D5,B1:B100 ))))
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


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

counting formula
 

Glad to help. For future reference it's always nice to fully state your
problem to begin with.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tendresse" wrote in message
...
Thank you guys for all your help ... muchly appreciated.
I used the formula below and it worked brilliantly.

Don, your formula assumes the word Dave is always at the beginning of the
cell. That's my mistake, i didn't mention in my question that there could
be
entries like 'From Dave'. But thank you very much, i'm sure it will come a
situation where i'll need to apply the LEFT function. Now i have the
answer
in advance.

Thanks again ... Tendresse

"RagDyeR" wrote:

To make the formula more robust, since the Find() function is case
sensitive, you can use the Search() function instead:

=SUMPRODUCT((A1:A100=C5)*(ISNUMBER(SEARCH(D5,B1:B1 00))))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"RagDyeR" wrote in message
...
Say you put the first name in a particular cell, say D5, then try this:

=SUMPRODUCT((A1:A100=C5)*(ISNUMBER(FIND(D5,B1:B100 ))))
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


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