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. 


Thread Tools  Display Modes 
#1




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




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




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




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




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




Can you just keyin 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




Perhaps try this sample file with the implemented formula inside:
http://flypicture.com/p.cfm?id=69459 (Rightclick 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




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




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




Hi Max,
Indeed I was, kicking out the z's. Unfortunately, Excel is not that smart. It is the equivalent of you putting in semicolons, 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 semicolons, 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  


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 02:37 AM 
Count number to reach a cumulative value  Bruce  Excel Worksheet Functions  5  January 25th 05 06:14 PM 