Counting cells if special criterias are true

[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

[Axel]

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

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

"a94andwi" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
in
> message news:[email protected]...
> >
> > 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
> >
>
>

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

The calculation should be like this:
For Row 1 we will get this output: 300000/256 = 1171 which is over 35 and should not be countet.
For Row 2 we will get this output: 2000/6000 = 0,33 which is under 35 and should be countet.
I want to know which parts that have a bad turn over and my goal is 35 times per year. That is why I use the 35 value.

I hope this explanes more.

/Anders

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

"a94andwi" <[email protected]> wrote in
message news:[email protected]...
>
> 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

[a94andwi]

Hello Bob.

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


UPDATE:

I found the error. it should be a count-function, not a sum-function!!!

Thank you. Finally I got it.

[Bob Phillips]

Post your formula.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"a94andwi" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[a94andwi]

Here is the final formula:

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

Thanks again.

[Bob Phillips]

Change COUNT to SUM as I gave you.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"a94andwi" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[a94andwi]

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

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"a94andwi" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[a94andwi]

Hello Bob.

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

/Anders