• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding custom formatted value in a spread sheet which is not visible

chandra.asodi

New Member
HI,


I daily deal with multiple spread sheets related billing. My question is


I have entered a different values from A1 to A10 and summed up the total. E.g 1,2,3,4,5,6,7,8,9,10 and the total is 55. I have selected cell no A5 which is a value of 5. I formatted the cell-Selected "Number" "Custom" type as ,,,. and the value in A5 visilbe as ",".

The visible no from A1 to A10 as 1,2,3,4,,,6,7,8,9,10 and the total as 55. It is supposed to 50 only if i check with a hard copy of the same with a calculator


My question is "Is there any fromula to find the cell (A5)with the this type of format.


Thanks in advance
 
Formatting the cell only changes the appearance, A5 still have a value of 5 wether you change the format to display a comma, text, or even another number! The only true way to not count it is to either delete the 5 from the cell, or use one of these formulas:


=SUM(A1:A4,A6:10)


=SUMIF(A1:A10,"<>5")


Which leads to, why are you not adding that cell? If there's some specific criteria, you'll probably want to use the SUMIF function in some other method.
 
Back
Top