Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif or Sumproduct
I need to count all cells in one column containing "FL" anywhere in the
sentence if the condition in another column is "Y" |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif or Sumproduct
With:
A1:A10 containing the FL cells B1:B10 containing the Y cells Try this: D1: FL D2: Y D3: =SUMPRODUCT(--((LEN(A1:A10)-LEN(SUBSTITUTE(UPPER(A1:A10),UPPER(D1),"")))<LEN( A1:A10))*(B1:B10=D2)) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Harley" wrote: I need to count all cells in one column containing "FL" anywhere in the sentence if the condition in another column is "Y" |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif or Sumproduct
I would introduce 2 helper columns. Of course it can be done many other
ways, but this way it is easy to check that your formula does what it is supposed to do. If your "Y"s are in A and your text in B, Then this in C: =IF(AND(A1="y",NOT(ISERROR(C1))),1,0) and this in D: =IF(AND(A1="y",NOT(ISERROR(C1))),1,0) both copied down as far as needed Sum column D -- Kind regards, Niek Otten "Harley" wrote in message ... I need to count all cells in one column containing "FL" anywhere in the sentence if the condition in another column is "Y" |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif or Sumproduct
Try something like the following...
=SUMPRODUCT(--(ISNUMBER(FIND("FL",A1:A10))),--(B1:B10="Y")) Note that FIND is case sensitive. If you don't want the case to be sensitive, use SEARCH instead. Hope this helps! In article , Harley wrote: I need to count all cells in one column containing "FL" anywhere in the sentence if the condition in another column is "Y" |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif or Sumproduct
THAT's what I was trying to think of!
It completely eluded me. *********** Regards, Ron XL2002, WinXP-Pro "Domenic" wrote: Try something like the following... =SUMPRODUCT(--(ISNUMBER(FIND("FL",A1:A10))),--(B1:B10="Y")) Note that FIND is case sensitive. If you don't want the case to be sensitive, use SEARCH instead. Hope this helps! In article , Harley wrote: I need to count all cells in one column containing "FL" anywhere in the sentence if the condition in another column is "Y" |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif or Sumproduct
I know that feeling... :)
In article , "Ron Coderre" wrote: THAT's what I was trying to think of! It completely eluded me. *********** Regards, Ron XL2002, WinXP-Pro "Domenic" wrote: Try something like the following... =SUMPRODUCT(--(ISNUMBER(FIND("FL",A1:A10))),--(B1:B10="Y")) Note that FIND is case sensitive. If you don't want the case to be sensitive, use SEARCH instead. Hope this helps! In article , Harley wrote: I need to count all cells in one column containing "FL" anywhere in the sentence if the condition in another column is "Y" |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif or Sumproduct
This did work - Thanks. I do have one situation where I need the formula to
find "FLH" or "FLT" under the same scenrio. Any thoughts? Thanks again, Harley "Domenic" wrote: Try something like the following... =SUMPRODUCT(--(ISNUMBER(FIND("FL",A1:A10))),--(B1:B10="Y")) Note that FIND is case sensitive. If you don't want the case to be sensitive, use SEARCH instead. Hope this helps! In article , Harley wrote: I need to count all cells in one column containing "FL" anywhere in the sentence if the condition in another column is "Y" |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif or Sumproduct
Try...
=SUMPRODUCT((ISNUMBER(FIND({"FLH","FLT"},A1:A10))) *(B1:B10="Y")) Hope this helps! In article , Harley wrote: This did work - Thanks. I do have one situation where I need the formula to find "FLH" or "FLT" under the same scenrio. Any thoughts? Thanks again, Harley "Domenic" wrote: Try something like the following... =SUMPRODUCT(--(ISNUMBER(FIND("FL",A1:A10))),--(B1:B10="Y")) Note that FIND is case sensitive. If you don't want the case to be sensitive, use SEARCH instead. Hope this helps! In article , Harley wrote: I need to count all cells in one column containing "FL" anywhere in the sentence if the condition in another column is "Y" |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif or Sumproduct
That got it!!
Thanks! "Domenic" wrote: Try... =SUMPRODUCT((ISNUMBER(FIND({"FLH","FLT"},A1:A10))) *(B1:B10="Y")) Hope this helps! In article , Harley wrote: This did work - Thanks. I do have one situation where I need the formula to find "FLH" or "FLT" under the same scenrio. Any thoughts? Thanks again, Harley "Domenic" wrote: Try something like the following... =SUMPRODUCT(--(ISNUMBER(FIND("FL",A1:A10))),--(B1:B10="Y")) Note that FIND is case sensitive. If you don't want the case to be sensitive, use SEARCH instead. Hope this helps! In article , Harley wrote: I need to count all cells in one column containing "FL" anywhere in the sentence if the condition in another column is "Y" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multi-conditions with SUMPRODUCT and COUNTIF | Excel Worksheet Functions | |||
SumProduct or CountIf | Excel Worksheet Functions | |||
countif, sumproduct | New Users to Excel | |||
Using COUNTIF with 2 criteria - SUMPRODUCT? | Excel Worksheet Functions | |||
SUMPRODUCT & COUNTIF | Excel Worksheet Functions |