Counting cells if special criterias are true
 

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


--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile: http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=535898

Counting cells if special criterias are true
 

Hi

try following:

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

regards
Axel

"a94andwi" wrote:


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


--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile: http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=535898

Counting cells if special criterias are true
 

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)

"a94andwi" wrote in
message ...

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


--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile:
http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=535898

Counting cells if special criterias are true
 

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" wrote in message
...
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)

"a94andwi" wrote
in
message ...

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


--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile:
http://www.excelforum.com/member.php...o&userid=21077
View this thread:
http://www.excelforum.com/showthread...hreadid=535898

Counting cells if special criterias are true
 

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

Counting cells if special criterias are true
 

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)

"a94andwi" wrote in
message ...

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

Counting cells if special criterias are true
 

Hello Bob.

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


--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile: http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=535898

Counting cells if special criterias are true
 

Post your formula.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"a94andwi" wrote in
message ...

Hello Bob.

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


--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile:
http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=535898

Counting cells if special criterias are true
 

Here is the final formula:

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

Thanks again.


--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile: http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=535898

Counting cells if special criterias are true
 

Change COUNT to SUM as I gave you.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"a94andwi" wrote in
message ...

Here is the final formula:

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

Thanks again.


--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile:
http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=535898

Counting cells if special criterias are true
 

Hello Bob.

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

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

/Anders


--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile: http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=535898

Counting cells if special criterias are true
 

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"a94andwi" wrote in
message ...

Hello Bob.

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

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

/Anders


--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile:
http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=535898

Counting cells if special criterias are true
 

Hello Bob.

That is correct. I only want to count the visible data.

/Anders


--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile: http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=535898