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
|