Counting number and text values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
=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.
 
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
 
=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?
 
Back
Top