| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula to read contents
Hi
Does anyone know ho to write an excel formula that will count cells if they contain part of the specified information. ie, I want to count if they include '1234' which is in box A1, but the fields I'm searching in may have 'AB1234', but I still want it to be able to count them if they include the details in A1. I hope that makes sense!! Thank you |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel formula to read contents
One way:
=COUNTIF(B:B,"*" & A1 & "*") In article , Fiona wrote: Hi Does anyone know ho to write an excel formula that will count cells if they contain part of the specified information. ie, I want to count if they include '1234' which is in box A1, but the fields I'm searching in may have 'AB1234', but I still want it to be able to count them if they include the details in A1. I hope that makes sense!! Thank you |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel formula to read contents
Thank you!
"JE McGimpsey" wrote: One way: =COUNTIF(B:B,"*" & A1 & "*") In article , Fiona wrote: Hi Does anyone know ho to write an excel formula that will count cells if they contain part of the specified information. ie, I want to count if they include '1234' which is in box A1, but the fields I'm searching in may have 'AB1234', but I still want it to be able to count them if they include the details in A1. I hope that makes sense!! Thank you |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel formula to read contents
Thank you for you reply, it worked. Is there anyway I can get it to read 2
different boxes at the same time, I'm not sure how to link it? Thank you "JE McGimpsey" wrote: One way: =COUNTIF(B:B,"*" & A1 & "*") In article , Fiona wrote: Hi Does anyone know ho to write an excel formula that will count cells if they contain part of the specified information. ie, I want to count if they include '1234' which is in box A1, but the fields I'm searching in may have 'AB1234', but I still want it to be able to count them if they include the details in A1. I hope that makes sense!! Thank you |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel formula to read contents
And what would you do if it read 2 different boxes (cells?).
Are you looking for the count of each? I.e, if A1=1234, A2=abc, does B1=abc1234 count as 2? =COUNTIF(B:B,"*" & A1 & "*") + =COUNTIF(B:B,"*" & A2 & "*") or does it count as 1, but only if it contains both in a certain arrangement (e.g., "abc1234"? =COUNTIF(B:B,"*" & A1 & A2 & "*") or does it count as 1 if it contains both, but in any arrangement (e.g, "def123456abc")? =SUMPRODUCT(--ISNUMBER(SEARCH(A1,B1:B100)), --ISNUMBER(SEARCH(A2,B1:B100))) or something else? In article , Fiona wrote: Thank you for you reply, it worked. Is there anyway I can get it to read 2 different boxes at the same time, I'm not sure how to link it? Thank you "JE McGimpsey" wrote: One way: =COUNTIF(B:B,"*" & A1 & "*") In article , Fiona wrote: Hi Does anyone know ho to write an excel formula that will count cells if they contain part of the specified information. ie, I want to count if they include '1234' which is in box A1, but the fields I'm searching in may have 'AB1234', but I still want it to be able to count them if they include the details in A1. I hope that makes sense!! Thank you |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel formula to read contents
Hi
I want it to count if A1:A100 = B2, and if C1:C100 = D2, I want it to only count if it contains B2 and D2, or part of it, together. Thank you "JE McGimpsey" wrote: And what would you do if it read 2 different boxes (cells?). Are you looking for the count of each? I.e, if A1=1234, A2=abc, does B1=abc1234 count as 2? =COUNTIF(B:B,"*" & A1 & "*") + =COUNTIF(B:B,"*" & A2 & "*") or does it count as 1, but only if it contains both in a certain arrangement (e.g., "abc1234"? =COUNTIF(B:B,"*" & A1 & A2 & "*") or does it count as 1 if it contains both, but in any arrangement (e.g, "def123456abc")? =SUMPRODUCT(--ISNUMBER(SEARCH(A1,B1:B100)), --ISNUMBER(SEARCH(A2,B1:B100))) or something else? In article , Fiona wrote: Thank you for you reply, it worked. Is there anyway I can get it to read 2 different boxes at the same time, I'm not sure how to link it? Thank you "JE McGimpsey" wrote: One way: =COUNTIF(B:B,"*" & A1 & "*") In article , Fiona wrote: Hi Does anyone know ho to write an excel formula that will count cells if they contain part of the specified information. ie, I want to count if they include '1234' which is in box A1, but the fields I'm searching in may have 'AB1234', but I still want it to be able to count them if they include the details in A1. I hope that makes sense!! Thank you |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel formula to read contents
Modify my last example for your ranges/criteria.
One In article , Fiona wrote: Hi I want it to count if A1:A100 = B2, and if C1:C100 = D2, I want it to only count if it contains B2 and D2, or part of it, together. Thank you "JE McGimpsey" wrote: And what would you do if it read 2 different boxes (cells?). Are you looking for the count of each? I.e, if A1=1234, A2=abc, does B1=abc1234 count as 2? =COUNTIF(B:B,"*" & A1 & "*") + =COUNTIF(B:B,"*" & A2 & "*") or does it count as 1, but only if it contains both in a certain arrangement (e.g., "abc1234"? =COUNTIF(B:B,"*" & A1 & A2 & "*") or does it count as 1 if it contains both, but in any arrangement (e.g, "def123456abc")? =SUMPRODUCT(--ISNUMBER(SEARCH(A1,B1:B100)), --ISNUMBER(SEARCH(A2,B1:B100))) or something else? In article , Fiona wrote: Thank you for you reply, it worked. Is there anyway I can get it to read 2 different boxes at the same time, I'm not sure how to link it? Thank you "JE McGimpsey" wrote: One way: =COUNTIF(B:B,"*" & A1 & "*") In article , Fiona wrote: Hi Does anyone know ho to write an excel formula that will count cells if they contain part of the specified information. ie, I want to count if they include '1234' which is in box A1, but the fields I'm searching in may have 'AB1234', but I still want it to be able to count them if they include the details in A1. I hope that makes sense!! Thank you |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel formula to read contents
Use JE's last formula and change the cell references, that would be
something like =SUMPRODUCT(--ISNUMBER(SEARCH(B2,A1:A100)),--ISNUMBER(SEARCH(D2,C1:C100))) -- Regards, Peo Sjoblom "Fiona" wrote in message ... Hi I want it to count if A1:A100 = B2, and if C1:C100 = D2, I want it to only count if it contains B2 and D2, or part of it, together. Thank you "JE McGimpsey" wrote: And what would you do if it read 2 different boxes (cells?). Are you looking for the count of each? I.e, if A1=1234, A2=abc, does B1=abc1234 count as 2? =COUNTIF(B:B,"*" & A1 & "*") + =COUNTIF(B:B,"*" & A2 & "*") or does it count as 1, but only if it contains both in a certain arrangement (e.g., "abc1234"? =COUNTIF(B:B,"*" & A1 & A2 & "*") or does it count as 1 if it contains both, but in any arrangement (e.g, "def123456abc")? =SUMPRODUCT(--ISNUMBER(SEARCH(A1,B1:B100)), --ISNUMBER(SEARCH(A2,B1:B100))) or something else? In article , Fiona wrote: Thank you for you reply, it worked. Is there anyway I can get it to read 2 different boxes at the same time, I'm not sure how to link it? Thank you "JE McGimpsey" wrote: One way: =COUNTIF(B:B,"*" & A1 & "*") In article , Fiona wrote: Hi Does anyone know ho to write an excel formula that will count cells if they contain part of the specified information. ie, I want to count if they include '1234' which is in box A1, but the fields I'm searching in may have 'AB1234', but I still want it to be able to count them if they include the details in A1. I hope that makes sense!! Thank you |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Read- only Protect Folders and contents in WIN2K Server | Excel Discussion (Misc queries) | |||
| How do I make a formula read a result rather than a formula | Excel Discussion (Misc queries) | |||
| Excel formula to find position of the contents of a cell within a column. | Excel Discussion (Misc queries) | |||
| How would this formula read | Excel Discussion (Misc queries) | |||
| Cell contents vs. Formula contents | Excel Discussion (Misc queries) | |||
Linear Mode
