# Calculate only with cells which contain numbers

1. ## Calculate only with cells which contain numbers

Dear all!
I couldn't find the solution so far, so I write a new thread (hope it is on the right place).

I am preparing some automatic form for field data.
The problem is that I wont to calculate only with cells which contain numbers.
For example, if I measure 4 sets of fractures in the field and later I want to calculate some trigonometric functions,
how can I use only cells which contain this filed data - of course automatically?
In example, you can see that trigonometric functions are calculated also for empty cells,
and the total result is wrong, because for example Cos0=1, but in fact there is no data for this empty cells.

hope you understand me   Register To Reply

2. ## Re: Calculate only with cells which contain numbers

Hey mkrac,

You could tell Excel not to count "1" values in your sum formula like this: ``Please Login or Register  to view this content.``
which gives you the answer you're looking for....  Register To Reply

3. ## Re: Calculate only with cells which contain numbers

Hi - Welcome to the forum. By definition excel treats a blank cell as 0, hence when you apply COS function to a blank cell it returns 1, which though trignometrically is correct but is not the ideal result. I think my assessing the length of the cell we can overcome this.

Try the below in cell B16

Formula:  `Please Login or Register  to view this content.`

and this in cell C16:

Formula:  `Please Login or Register  to view this content.`

Does this help?

P.S.: From my understanding this will give an error if you just type a space in your data range and this could be overcome by using trim.  Register To Reply

4. ## Re: Calculate only with cells which contain numbers

Dear BB1972,

thanks for replay.
The problem is that field result can also be 0 (A=0 and D=0) and in this case ignoring 1 would be an error  Register To Reply

5. ## Re: Calculate only with cells which contain numbers

Mr kbkumar,

thanks for your suggestion, but your formula is not working in my excel file.
Can you please try it on attached file and than attach it, because I can't find error (not so familiar with If, and... Thanks  Register To Reply

6. ## Re: Calculate only with cells which contain numbers

Attached. Let me know if this works for you.  Register To Reply

7. ## Re: Calculate only with cells which contain numbers

Since both are in same length, use SUMIF, to check E2:E11 is NOT Blank, then SUM B16:B25

=SUMIF(E2:E11,"<>",B16:B25)

Or SUMPRODUCT with just one table.  Register To Reply

8. ## Re: Calculate only with cells which contain numbers

Mr. kbkumar,

now it works, thank you!  Register To Reply

9. ## Re: Calculate only with cells which contain numbers Originally Posted by Haseeb A Since both are in same length, use SUMIF, to check E2:E11 is NOT Blank, then SUM B16:B25

=SUMIF(E2:E11,"<>",B16:B25)

Or SUMPRODUCT with just one table.

Mr. Haseeb,

thank you for your suggestion and your time, but I attached this table only as an example, in fact I don't need sum of ax, ay, az.
But I learned something :-)
I will play a little bit with table and than attach final version if I succeed to finish it or even if I don't finish it.

Sorry  Register To Reply

10. ## Re: Calculate only with cells which contain numbers  Register To Reply