Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting cells with data | Excel Discussion (Misc queries) | |||
COUNTING CELLS GIVEN OTHER CELLS? | Excel Discussion (Misc queries) | |||
Counting cells in Excel? | Excel Discussion (Misc queries) | |||
Counting cells, similar values | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |