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

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

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

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

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

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