A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

COUNT IF NOT EQUAL TO ZERO



 
 
Thread Tools Display Modes
  #1  
Old June 23rd 05, 09:39 AM
ellebelle
external usenet poster
 
Posts: n/a
Default COUNT IF NOT EQUAL TO ZERO

I would like to count the number of cells in a column that does not equal
zero and then multiply each count by an adjacent number.

EG.
no of shots days per shot
6 3
0 0
3 5

i want to calculate the "days per shot" column that does not equal zero and
then multiply each count by the no. of shot. therefore 1*6+1*3 = 9.

The main problem i am having is how to count when something does not equal a
certain argument - COUNTIF NOT????

any ideas?

Ads
  #2  
Old June 23rd 05, 10:00 AM
Max
external usenet poster
 
Posts: n/a
Default

Assuming the sample data is in A2:B4
Try: =SUMPRODUCT((A2:A4)*(B2:B4<>0))

Adapt the ranges to suit. Note that you can't use entire col references
(A:A, B:B, etc) in SUMPRODUCT
--
Rgds
Max
xl 97
---
GMT+8, 1 22' N 103 45' E
xdemechanik <at>yahoo<dot>com
----
"ellebelle" > wrote in message
...
> I would like to count the number of cells in a column that does not equal
> zero and then multiply each count by an adjacent number.
>
> EG.
> no of shots days per shot
> 6 3
> 0 0
> 3 5
>
> i want to calculate the "days per shot" column that does not equal zero

and
> then multiply each count by the no. of shot. therefore 1*6+1*3 = 9.
>
> The main problem i am having is how to count when something does not equal

a
> certain argument - COUNTIF NOT????
>
> any ideas?
>



  #3  
Old June 23rd 05, 10:31 AM
ellebelle
external usenet poster
 
Posts: n/a
Default

thanks - silly question but how do you inset greater than zero in a function
OR does not equal zero?

e

"Max" wrote:

> Assuming the sample data is in A2:B4
> Try: =SUMPRODUCT((A2:A4)*(B2:B4<>0))
>
> Adapt the ranges to suit. Note that you can't use entire col references
> (A:A, B:B, etc) in SUMPRODUCT
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "ellebelle" > wrote in message
> ...
> > I would like to count the number of cells in a column that does not equal
> > zero and then multiply each count by an adjacent number.
> >
> > EG.
> > no of shots days per shot
> > 6 3
> > 0 0
> > 3 5
> >
> > i want to calculate the "days per shot" column that does not equal zero

> and
> > then multiply each count by the no. of shot. therefore 1*6+1*3 = 9.
> >
> > The main problem i am having is how to count when something does not equal

> a
> > certain argument - COUNTIF NOT????
> >
> > any ideas?
> >

>
>
>

  #4  
Old June 23rd 05, 10:36 AM
Max
external usenet poster
 
Posts: n/a
Default

> .. greater than zero
Key in: >0

> .. does not equal zero

Key in: <>0
("<>" means: does not equal)
--
Rgds
Max
xl 97
---
GMT+8, 1 22' N 103 45' E
xdemechanik <at>yahoo<dot>com
----
"ellebelle" > wrote in message
...
> thanks - silly question but how do you inset greater than zero in a

function
> OR does not equal zero?



  #5  
Old June 23rd 05, 10:49 AM
ellebelle
external usenet poster
 
Posts: n/a
Default

I have tried this and many combinations - it is not working.

do I include the & symbol and the ; symbol?

e

"Max" wrote:

> > .. greater than zero

> Key in: >0
>
> > .. does not equal zero

> Key in: <>0
> ("<>" means: does not equal)
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "ellebelle" > wrote in message
> ...
> > thanks - silly question but how do you inset greater than zero in a

> function
> > OR does not equal zero?

>
>
>

  #6  
Old June 23rd 05, 11:13 AM
Max
external usenet poster
 
Posts: n/a
Default

Can you just key-in from the keyboard ?

Or try a direct copy of the formula from the post
and then paste into a cell in your sheet ?

I don't know what is not working for you ..
--
Rgds
Max
xl 97
---
GMT+8, 1 22' N 103 45' E
xdemechanik <at>yahoo<dot>com
----
"ellebelle" > wrote in message
...
> I have tried this and many combinations - it is not working.
> do I include the & symbol and the ; symbol?



  #7  
Old June 23rd 05, 11:23 AM
Max
external usenet poster
 
Posts: n/a
Default

Perhaps try this sample file with the implemented formula inside:
http://flypicture.com/p.cfm?id=69459

(Right-click on the link: "Download File"
at the top in the page, just above the ads)

File: ellebelle_wksht.xls

--
Rgds
Max
xl 97
---
GMT+8, 1 22' N 103 45' E
xdemechanik <at>yahoo<dot>com
----


  #8  
Old June 23rd 05, 11:46 AM
Bob Phillips
external usenet poster
 
Posts: n/a
Default

What &, what ;?

Do you have a continental version of Excel? If so, it would be something
like

=IF(A1>0;"Yes";"No")

& only comes in with COUNTIF/SUMIF and comparison to a cell

=SUMIF($A:$A;">"&C1,$B:$B)

--
HTH

Bob Phillips

"ellebelle" > wrote in message
...
> I have tried this and many combinations - it is not working.
>
> do I include the & symbol and the ; symbol?
>
> e
>
> "Max" wrote:
>
> > > .. greater than zero

> > Key in: >0
> >
> > > .. does not equal zero

> > Key in: <>0
> > ("<>" means: does not equal)
> > --
> > Rgds
> > Max
> > xl 97
> > ---
> > GMT+8, 1 22' N 103 45' E
> > xdemechanik <at>yahoo<dot>com
> > ----
> > "ellebelle" > wrote in message
> > ...
> > > thanks - silly question but how do you inset greater than zero in a

> > function
> > > OR does not equal zero?

> >
> >
> >



  #9  
Old June 24th 05, 02:05 AM
Max
external usenet poster
 
Posts: n/a
Default

You're probably in deep slumber now, Bob <bg>, but when you're up ...
If the OP opens the file posted in her "continental version" of Excel, would
the formulas therein be automatically converted in terms of the commas to
semicolons, etc ? Thanks.
--
Rgds
Max
xl 97
---
GMT+8, 1 22' N 103 45' E
xdemechanik <at>yahoo<dot>com
----


  #10  
Old June 24th 05, 10:32 AM
Bob Phillips
external usenet poster
 
Posts: n/a
Default

Hi Max,

Indeed I was, kicking out the z's.

Unfortunately, Excel is not that smart. It is the equivalent of you putting
in semi-colons, like
=IF(A1=17;A1;B1)
same error.

VBA has a strange twist. If you set a formula there, you have to use
English, like
Actyivcell.Formula = SUM(A1:A10)
and it gets translated, so German comes out as =SOMME(A1:A10). I am not sure
if you also have to use commas and it gets translated to semi-colons, I
don't have a continental version of an OS and Excel, but I doubt it as I
would have thought VBA uses the regional settings (but that is purely a
guess, and haven written has set doubt in my mind :-)),.

--
HTH

Bob Phillips

"Max" > wrote in message
...
> You're probably in deep slumber now, Bob <bg>, but when you're up ...
> If the OP opens the file posted in her "continental version" of Excel,

would
> the formulas therein be automatically converted in terms of the commas to
> semicolons, etc ? Thanks.
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1 22' N 103 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Count Position of Filtered TEXT cells in a column Sam via OfficeKB.com Excel Worksheet Functions 8 May 18th 05 04:23 AM
Count Position of Filtered TEXT cells in a column Sam via OfficeKB.com Excel Worksheet Functions 0 May 15th 05 08:14 PM
Count cells with length not equal to 7 cottage6 Excel Worksheet Functions 6 April 7th 05 09:04 PM
SUMPRODUCT Formula to Count Row of data Below Matched Criteria Sam via OfficeKB.com Excel Worksheet Functions 8 February 3rd 05 01:37 AM
Count number to reach a cumulative value Bruce Excel Worksheet Functions 5 January 25th 05 05:14 PM


All times are GMT +1. The time now is 08:57 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.