Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting formula
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula / Counting | Excel Worksheet Functions | |||
Counting formula | Excel Discussion (Misc queries) | |||
Counting formula | Excel Worksheet Functions | |||
Counting Formula | Excel Worksheet Functions | |||
Help with counting formula | Excel Discussion (Misc queries) |