Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting number and text values
Can someone help me with this? I am trying to count and add number of
occurrences (marked by 1 or zeros) for a specific client(using bl* to find all clients starting with bl name) I am using sum function =SUM(IF(D17:D410="bl*",IF(A17:A410="1",1,0))) but return value is always 0 I also tried =COUNT(IF((D17:D200="bl*")*(A17:A2000=1),A17:A2000 )) but result is also incorrect. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting number and text values
Try this:
=SUMPRODUCT(--(A17:A410=1),--(LEFT(D17:D410,2)="bl")) -- Biff Microsoft Excel MVP "Greg" wrote in message ... Can someone help me with this? I am trying to count and add number of occurrences (marked by 1 or zeros) for a specific client(using bl* to find all clients starting with bl name) I am using sum function =SUM(IF(D17:D410="bl*",IF(A17:A410="1",1,0))) but return value is always 0 I also tried =COUNT(IF((D17:D200="bl*")*(A17:A2000=1),A17:A2000 )) but result is also incorrect. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting number and text values
No still comming with value of 0. Here are 2 columns:
1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 0 United 1 United 0 United 1 United 1 United 1 Delta 0 Delta 1 Delta 0 Delta 1 Delta 0 Delta 0 Delta I would like to sum number of 1 for United "T. Valko" wrote: Try this: =SUMPRODUCT(--(A17:A410=1),--(LEFT(D17:D410,2)="bl")) -- Biff Microsoft Excel MVP "Greg" wrote in message ... Can someone help me with this? I am trying to count and add number of occurrences (marked by 1 or zeros) for a specific client(using bl* to find all clients starting with bl name) I am using sum function =SUM(IF(D17:D410="bl*",IF(A17:A410="1",1,0))) but return value is always 0 I also tried =COUNT(IF((D17:D200="bl*")*(A17:A2000=1),A17:A2000 )) but result is also incorrect. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting number and text values
=sumif(b:b,"united",a:a)
or =sumif(b:b,"united*",a:a) or =sumif(b:b,x99&"*",a:a) if you put the prefix you wanted in x99. if those 1's are really numbers and not text. Greg wrote: No still comming with value of 0. Here are 2 columns: 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 0 United 1 United 0 United 1 United 1 United 1 Delta 0 Delta 1 Delta 0 Delta 1 Delta 0 Delta 0 Delta I would like to sum number of 1 for United "T. Valko" wrote: Try this: =SUMPRODUCT(--(A17:A410=1),--(LEFT(D17:D410,2)="bl")) -- Biff Microsoft Excel MVP "Greg" wrote in message ... Can someone help me with this? I am trying to count and add number of occurrences (marked by 1 or zeros) for a specific client(using bl* to find all clients starting with bl name) I am using sum function =SUM(IF(D17:D410="bl*",IF(A17:A410="1",1,0))) but return value is always 0 I also tried =COUNT(IF((D17:D200="bl*")*(A17:A2000=1),A17:A2000 )) but result is also incorrect. Thanks -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting number and text values
Try this instead. It looked like you were searching for BL and not b1.
=SUMPRODUCT(--(A1:A20=1),--(TEXT(B1:B20,2)=B1)) Cell B1 containing the info you are looking for. John "Greg" wrote in message ... No still comming with value of 0. Here are 2 columns: 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 0 United 1 United 0 United 1 United 1 United 1 Delta 0 Delta 1 Delta 0 Delta 1 Delta 0 Delta 0 Delta I would like to sum number of 1 for United "T. Valko" wrote: Try this: =SUMPRODUCT(--(A17:A410=1),--(LEFT(D17:D410,2)="bl")) -- Biff Microsoft Excel MVP "Greg" wrote in message ... Can someone help me with this? I am trying to count and add number of occurrences (marked by 1 or zeros) for a specific client(using bl* to find all clients starting with bl name) I am using sum function =SUM(IF(D17:D410="bl*",IF(A17:A410="1",1,0))) but return value is always 0 I also tried =COUNT(IF((D17:D200="bl*")*(A17:A2000=1),A17:A2000 )) but result is also incorrect. Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting number and text values
=SUMPRODUCT(--(A17:A410=1),--(D17:D410)="United"))
If that is giving zero, then you ought to check what is actually in your columns. If you think A17 has a 1, try =A17=1 If this gives FALSE, rather than TRUE, then look at what is in the formula bar when A17 is selected. Might it be text? Check =ISNUMBER(A17) or =ISTEXT(A17) If you think D17 has "United", try =D17="United" If that is FALSE, look in the formula bar when D17 is selected and see whether there are spurious spaces or other non-printing characters before or after the word "United". Does =LEN(D17) come out as 6? -- David Biddulph "Greg" wrote in message ... No still comming with value of 0. Here are 2 columns: 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 1 United 0 United 1 United 0 United 1 United 1 United 1 Delta 0 Delta 1 Delta 0 Delta 1 Delta 0 Delta 0 Delta I would like to sum number of 1 for United "T. Valko" wrote: Try this: =SUMPRODUCT(--(A17:A410=1),--(LEFT(D17:D410,2)="bl")) -- Biff Microsoft Excel MVP "Greg" wrote in message ... Can someone help me with this? I am trying to count and add number of occurrences (marked by 1 or zeros) for a specific client(using bl* to find all clients starting with bl name) I am using sum function =SUM(IF(D17:D410="bl*",IF(A17:A410="1",1,0))) but return value is always 0 I also tried =COUNT(IF((D17:D200="bl*")*(A17:A2000=1),A17:A2000 )) but result is also incorrect. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting number of times certain Text Values appear | Excel Discussion (Misc queries) | |||
Counting Text as a Number | Excel Worksheet Functions | |||
Counting a mixed text/number column based on text in another colum | Excel Discussion (Misc queries) | |||
counting unique items(values or text) | Excel Worksheet Functions | |||
Counting the number of values in a list between two specified valu | Excel Worksheet Functions |