# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  > [SOLVED] count if not equal to zero

## ellebelle

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?

----------


## Max

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" <ellebelle@discussions.microsoft.com> wrote in message
news:C73794B4-ACE0-498C-B140-2AD22B4A3F89@microsoft.com...
> 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?
>

----------


## ellebelle

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" <ellebelle@discussions.microsoft.com> wrote in message
> news:C73794B4-ACE0-498C-B140-2AD22B4A3F89@microsoft.com...
> > 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?
> >
>
>
>

----------


## Max

> .. 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" <ellebelle@discussions.microsoft.com> wrote in message
news:57A73FCC-D6AF-4702-A2BA-8FC18B831EDC@microsoft.com...
> thanks - silly question but how do you inset greater than zero in a
function
> OR does not equal zero?

----------


## ellebelle

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" <ellebelle@discussions.microsoft.com> wrote in message
> news:57A73FCC-D6AF-4702-A2BA-8FC18B831EDC@microsoft.com...
> > thanks - silly question but how do you inset greater than zero in a
> function
> > OR does not equal zero?
>
>
>

----------


## Max

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" <ellebelle@discussions.microsoft.com> wrote in message
news:2F2892E4-8C0A-4DDE-94CF-CB929050D08B@microsoft.com...
> I have tried this and many combinations - it is not working.
> do I include the & symbol  and the ; symbol?

----------


## Max

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

----------


## Bob Phillips

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" <ellebelle@discussions.microsoft.com> wrote in message
news:2F2892E4-8C0A-4DDE-94CF-CB929050D08B@microsoft.com...
> 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" <ellebelle@discussions.microsoft.com> wrote in message
> > news:57A73FCC-D6AF-4702-A2BA-8FC18B831EDC@microsoft.com...
> > > thanks - silly question but how do you inset greater than zero in a
> > function
> > > OR does not equal zero?
> >
> >
> >

----------


## Max

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

----------


## Bob Phillips

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" <demechanik@yahoo.com> wrote in message
news:OegN6jFeFHA.412@tk2msftngp13.phx.gbl...
> 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
> ----
>
>

----------


## Max

Thanks for your insights, Bob !

Guess we'll just have to take it from where the OP decides to continue or
re-join the thread discussions then (if ever) <g>.  But our best efforts to
help would certainly have been given, nonetheless ..
--
Rgds
Max
xl 97
---
GMT+8,  1° 22' N  103° 45' E
xdemechanik <at>yahoo<dot>com
----

----------


## Bob Phillips

Your comment prompts me to note how amazed I am that so many threads just
seem to tail off into nothing. How many times have you given a question as a
response, or a partial answer in the hope of getting the OP to clarify or
think more about, never to hear anymore/

Tis odd. They take the trouble to post here, but often seem to give up very
easily.

Bob

"Max" <demechanik@yahoo.com> wrote in message
news:%23D0nXALeFHA.2664@TK2MSFTNGP15.phx.gbl...
> Thanks for your insights, Bob !
>
> Guess we'll just have to take it from where the OP decides to continue or
> re-join the thread discussions then (if ever) <g>.  But our best efforts
to
> help would certainly have been given, nonetheless ..
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8,  1° 22' N  103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>

----------


## Max

> .. so many threads just seem to tail off into nothing ..
> .... They take the trouble to post here,
> but often seem to give up very easily ..

I'll second those perceptions. And it appears there's also so many "one hit"
threads around that responders never really know whether their responses did
get through to the OPs. An amazing lack of closure ..
--
Rgds
Max
xl 97
---
GMT+8,  1° 22' N  103° 45' E
xdemechanik <at>yahoo<dot>com
----

----------


## Homestar333

If you want to use greater than zero in a function like that just put it in quotes. e.g. if you want to only count the numbers in cells A2 through A10 if they are not zero use:

=COUNTIF(A2:A10,">0")

----------

