Hi; I have an easy one that stumps me.
I have been using =sumif(b4:b63,"name",i4:i63) for many entries, and there
is one client name that keeps resulting in zero or nothing.
I have a monthly sales list; col b is sold to cust. name and the invoice
value is in col.i there are several entries of the same client,
so I simply,do sumif but why is this one client name resultsing in 0 ? I
have checked spelling, commas, quotes etc. his name does have an apostropy ',
but I use it in the formula.
please help
michelle

One thing to try.
=sumif(trim(b4:b63),"name",i4:i63)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Michelle" wrote in message
...
Hi; I have an easy one that stumps me.
I have been using =sumif(b4:b63,"name",i4:i63) for many entries, and there
is one client name that keeps resulting in zero or nothing.
I have a monthly sales list; col b is sold to cust. name and the invoice
value is in col.i there are several entries of the same client,
so I simply,do sumif but why is this one client name resultsing in 0 ? I
have checked spelling, commas, quotes etc. his name does have an apostropy
',
but I use it in the formula.
please help
michelle

Hi Michelle,

Use the LEN() function to check if there's any character that isn't shown.
Use the TRIM() function to get rid of unwanted spaces.
Use CLEAN() to get rid of nonprintable characters
One thing to be aware of, certainly if the data originated from a website,
is the non-breaking space, look here for a solution:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Michelle" wrote in message
...
Hi; I have an easy one that stumps me.
I have been using =sumif(b4:b63,"name",i4:i63) for many entries, and there
is one client name that keeps resulting in zero or nothing.
I have a monthly sales list; col b is sold to cust. name and the invoice
value is in col.i there are several entries of the same client,
so I simply,do sumif but why is this one client name resultsing in 0 ? I
have checked spelling, commas, quotes etc. his name does have an apostropy
',
but I use it in the formula.
please help
michelle

Thank you,with this information I was able to find that the name had an extra
space at the end, since I copy and paste all the entries for this customer
were not working with the formula.

Once I cleared the space "bingo".
Thanks again!
Michelle

"Niek Otten" wrote:

Hi Michelle,

Use the LEN() function to check if there's any character that isn't shown.
Use the TRIM() function to get rid of unwanted spaces.
Use CLEAN() to get rid of nonprintable characters
One thing to be aware of, certainly if the data originated from a website,
is the non-breaking space, look here for a solution:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Michelle" wrote in message
...
Hi; I have an easy one that stumps me.
I have been using =sumif(b4:b63,"name",i4:i63) for many entries, and there
is one client name that keeps resulting in zero or nothing.
I have a monthly sales list; col b is sold to cust. name and the invoice
value is in col.i there are several entries of the same client,
so I simply,do sumif but why is this one client name resultsing in 0 ? I
have checked spelling, commas, quotes etc. his name does have an apostropy
',
but I use it in the formula.
please help
michelle