LinkBack URL
About LinkBacks
I need to sum cells that contain this formula: =if($c$5=0,"",c5*b5). I am getting a wrong data type value error.
I need to sum cells that contain this formula: =if($c$5=0,"",c5*b5). I am getting a wrong data type value error.
whats in c5 or d5
it would appear to be text
are you sure there are numbers there ?
try
=ISTEXT(C5)
=ISTEXT(B5)
or
ISNUMBER(C5)
ISNUMBER(B5)
if it is a number but has been formatted or imported as text , a few things you could do is
1) multiply by 1
2) Add 0
3) use data> text to columns > delimited > next >next - will turn text numbers to numbers, for the whole column - if an imported list
=if($c$5*1=0,"",(c5*1)*(b5*1))
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
This if statement will leave the cell blank if there is a zero value, otherwise it will calculate the formula. When it is left blank it is read as "". I need to include these cells in sum for future use.
=if( OR( C5=0, C5="") ,"",c5*b5)
so now if the cell C5 is blank or zero - you will get a blank returned
you could also test for B5
=if( OR( C5 = 0, C5 = "" , B5 = "" ) ,"",c5*b5)
you have $ around the cell - did you want to fix C5 regardless of where you copy the formula?
The original formula is correct, it returns a blank if the value is zero. I am trying to total some values (=c5+c8+c11+c13) and because the cells have a blank in them the formula will not sum them.
instead of using
+ use sum as that will ignore the blanks
=sum(c5,c8,c11,c13)
Ahhhhh that's it. Thank youSorry I wasn't clear in the beginning!! Have a great weekend.
your welcome, thanks for the rep
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks