Home |
Search |
Today's Posts |
|
#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? |
#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 <atyahoo<dotcom ---- "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 <atyahoo<dotcom ---- "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 <atyahoo<dotcom ---- "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 <atyahoo<dotcom ---- "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 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 <atyahoo<dotcom ---- "ellebelle" wrote in message ... I have tried this and many combinations - it is not working. do I include the & symbol and the ; symbol? |
#7
|
|||
|
|||
What &, what ;?
Do you have a continental version of Excel? If so, it would be something like =IF(A10;"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 <atyahoo<dotcom ---- "ellebelle" wrote in message ... thanks - silly question but how do you inset greater than zero in a function OR does not equal zero? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count cells with length not equal to 7 | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
Count number to reach a cumulative value | Excel Worksheet Functions |