Counting cells if special criterias are true

A

a94andwi

Hello.

I have a worksheet containing appr. 9000 part numbers that have four
different columns as: annual volume, dispatch quantity and turn over
speed.

I want to count all rows where these conditions are true:

(annual volume / dispatch quantity)>turn over speed

I thought I could do something like this but it turns out wrong:

=COUNTIF((annual volume/dispatch quantity); >turn over speed)

This does not work.
Is there a way that I can do this easily?

note: the " ;" is correct. In my excel I use ; instead of " , ".

Thanks in advance

/Anders
 
A

Axel

Hi

try following:

countif($firstrow$firstcolumn:$lastrow$lastcolumn;(annualvolume/dispatchquantity)>turnoverspeed)

regards
Axel
 
B

Bob Phillips

Try

=SUM(IF(dispatch quantity<>0,IF((annual volume / dispatch quantity)>turn
over speed,1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

Bob Phillips

oops

=SUM(IF(dispatch quantity<>0;IF((annual volume / dispatch quantity)>turn
over speed;1)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

Bob Phillips said:
Try

=SUM(IF(dispatch quantity<>0,IF((annual volume / dispatch quantity)>turn
over speed,1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

message news:[email protected]...
Click to expand...
 
A

a94andwi

Hello Bob.

I have tried your example but it get a #Value error. I don't know why
this has occured.
In dispatch quantities I have values such as: 1, 1000, 300 ,256 and so
on.
In annual volume I have values such as: 50000, 3000, 5000000 and so
on.
Turn over speed is one cell in a different sheet that is hard coded to
35.

What can be wrong with my data or with you example?
An example of data:
DQ AV
Row 1: 256 300000
Row 2: 6000 2000
 
B

Bob Phillips

As I said in my original reply, it is an array formula, so commit with
Ctrl-Shift-Enter, not just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
A

a94andwi

Hello Bob.

I tried the ctrl+shift+enter way but I still get the same #VALUE!
error.
 
B

Bob Phillips

Post your formula.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
A

a94andwi

Here is the final formula:

=COUNT(IF(dispatch quantity<>0; IF((annual quantity/dispatch
quantity)<Turn over speed;1)))

Thanks again.
 
B

Bob Phillips

Change COUNT to SUM as I gave you.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
A

a94andwi

Hello Bob.

I think you missed my input earlier that COUNT was the correc
function.

I still have a question though. If I filter the list it still return
the same figures. How do I include the advanced Filter that I am usin
for the 9000 part numbers?

/Ander
 
B

Bob Phillips

You mean that you only want the visible data to counted?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Top