# 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

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....

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.

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

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

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

Attached. Let me know if this works for you.

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.

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

Mr. kbkumar,

now it works, thank you!

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

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1