Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I count the occurrences of multiple conditions
In the help menu at the end of the formula there is 0,1 I do not know what
this is referencing could someone explain this to me please? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I count the occurrences of multiple conditions
hi, to count the occurrences of multiple conditions, try to use sumproduct.
=sumproduct(--(a2:a100="condition 1")*(b2:b100="condition 2")) hth regards from Brazil Marcelo "Debi" escreveu: In the help menu at the end of the formula there is 0,1 I do not know what this is referencing could someone explain this to me please? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I count the occurrences of multiple conditions
You will ahve to give a little more information and an example. What formula? What does the formula refer to, what are the values in the cells Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=562096 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I count the occurrences of multiple conditions
I have data that I want to count how many rows there are that meet 2 seperate
criteria for example I would like to count how many rows contain a CB in column b rows 4 through 309 and a 2 in column q rows 4 through rows 309. When I looked in the help menu it give an example of =sum(if(b5:b25,"northwind,if(c5:c25-"western",1,0))) first is this the correct formula and second what does the 1,0 represent at the end of the formula example given? "Dav" wrote: You will ahve to give a little more information and an example. What formula? What does the formula refer to, what are the values in the cells Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=562096 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I count the occurrences of multiple conditions
Hi Debi,
the 1,0 at the end of the formula means true,false, excel looks for the if question and return 1 if it's true of 0 if it's false, eg assuming that you have "apple" on cell a1, so =if(a1="apple",true,false) where in your case true = 1 and false = 0 you can also use functions here. to solve your problem I think the best way is use sumproduct =sumproduct(--(B4:B309="CB")*(Q4:A309=2)) HTH regards from Brazil Marcelo "Debi" escreveu: I have data that I want to count how many rows there are that meet 2 seperate criteria for example I would like to count how many rows contain a CB in column b rows 4 through 309 and a 2 in column q rows 4 through rows 309. When I looked in the help menu it give an example of =sum(if(b5:b25,"northwind,if(c5:c25-"western",1,0))) first is this the correct formula and second what does the 1,0 represent at the end of the formula example given? "Dav" wrote: You will ahve to give a little more information and an example. What formula? What does the formula refer to, what are the values in the cells Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=562096 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I count the occurrences of multiple conditions
Excellent that worked now if you could tell me how I would count the rows if
column B rows 4 throught 309 = CB and columns l through t rows 4 through 309 = 0 or = not blank or = blank. The criteria of 0. blank, not blank need to be either combined or seperate depending on the work sheet I am working in. Can you help me out on this one? "Marcelo" wrote: Hi Debi, the 1,0 at the end of the formula means true,false, excel looks for the if question and return 1 if it's true of 0 if it's false, eg assuming that you have "apple" on cell a1, so =if(a1="apple",true,false) where in your case true = 1 and false = 0 you can also use functions here. to solve your problem I think the best way is use sumproduct =sumproduct(--(B4:B309="CB")*(Q4:A309=2)) HTH regards from Brazil Marcelo "Debi" escreveu: I have data that I want to count how many rows there are that meet 2 seperate criteria for example I would like to count how many rows contain a CB in column b rows 4 through 309 and a 2 in column q rows 4 through rows 309. When I looked in the help menu it give an example of =sum(if(b5:b25,"northwind,if(c5:c25-"western",1,0))) first is this the correct formula and second what does the 1,0 represent at the end of the formula example given? "Dav" wrote: You will ahve to give a little more information and an example. What formula? What does the formula refer to, what are the values in the cells Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=562096 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I count the occurrences of multiple conditions
Debi,
if my understand is correct try: =sumproduct(--(B4:B309="CB")*(Q4:A309=2)*contif(l4:t309,"=0"))/sumproduct(--(B4:B309="CB")*(Q4:A309=2)) hth regards from Brazil Marcelo "Debi" escreveu: Excellent that worked now if you could tell me how I would count the rows if column B rows 4 throught 309 = CB and columns l through t rows 4 through 309 = 0 or = not blank or = blank. The criteria of 0. blank, not blank need to be either combined or seperate depending on the work sheet I am working in. Can you help me out on this one? "Marcelo" wrote: Hi Debi, the 1,0 at the end of the formula means true,false, excel looks for the if question and return 1 if it's true of 0 if it's false, eg assuming that you have "apple" on cell a1, so =if(a1="apple",true,false) where in your case true = 1 and false = 0 you can also use functions here. to solve your problem I think the best way is use sumproduct =sumproduct(--(B4:B309="CB")*(Q4:A309=2)) HTH regards from Brazil Marcelo "Debi" escreveu: I have data that I want to count how many rows there are that meet 2 seperate criteria for example I would like to count how many rows contain a CB in column b rows 4 through 309 and a 2 in column q rows 4 through rows 309. When I looked in the help menu it give an example of =sum(if(b5:b25,"northwind,if(c5:c25-"western",1,0))) first is this the correct formula and second what does the 1,0 represent at the end of the formula example given? "Dav" wrote: You will ahve to give a little more information and an example. What formula? What does the formula refer to, what are the values in the cells Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=562096 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I count the occurrences of multiple conditions
In your formula you reference Q4:A309 instead of Q4:Q309. Is this a typo or
should I use that range if so could you explain why? And if I do not wish to sum the ones in column q that contain a 2 and just want to count those in columns i through t can I just leave off that part of the formula? "Marcelo" wrote: Debi, if my understand is correct try: '=sumproduct(--(B4:B309="CB")*(Q4:A309=2)*contif(l4:t309,"=0"))/sumproduct(--(B4:B309="CB")*(Q4:A309=2)) hth regards from Brazil Marcelo "Debi" escreveu: Excellent that worked now if you could tell me how I would count the rows if column B rows 4 throught 309 = CB and columns l through t rows 4 through 309 = 0 or = not blank or = blank. The criteria of 0. blank, not blank need to be either combined or seperate depending on the work sheet I am working in. Can you help me out on this one? "Marcelo" wrote: Hi Debi, the 1,0 at the end of the formula means true,false, excel looks for the if question and return 1 if it's true of 0 if it's false, eg assuming that you have "apple" on cell a1, so '=if(a1="apple",true,false) where in your case true = 1 and false = 0 you can also use functions here. to solve your problem I think the best way is use sumproduct '=sumproduct(--(B4:B309="CB")*(Q4:A309=2)) HTH regards from Brazil Marcelo "Debi" escreveu: I have data that I want to count how many rows there are that meet 2 seperate criteria for example I would like to count how many rows contain a CB in column b rows 4 through 309 and a 2 in column q rows 4 through rows 309. When I looked in the help menu it give an example of '=sum(if(b5:b25,"northwind,if(c5:c25-"western",1,0))) first is this the correct formula and second what does the 1,0 represent at the end of the formula example given? "Dav" wrote: You will ahve to give a little more information and an example. What formula? What does the formula refer to, what are the values in the cells Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=562096 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I count the occurrences of multiple conditions
typo mistake, the column q on the formula is just to divede the product
hth regards from Brazil Marcelo "Debi" escreveu: In your formula you reference Q4:A309 instead of Q4:Q309. Is this a typo or should I use that range if so could you explain why? And if I do not wish to sum the ones in column q that contain a 2 and just want to count those in columns i through t can I just leave off that part of the formula? "Marcelo" wrote: Debi, if my understand is correct try: '=sumproduct(--(B4:B309="CB")*(Q4:A309=2)*contif(l4:t309,"=0"))/sumproduct(--(B4:B309="CB")*(Q4:A309=2)) hth regards from Brazil Marcelo "Debi" escreveu: Excellent that worked now if you could tell me how I would count the rows if column B rows 4 throught 309 = CB and columns l through t rows 4 through 309 = 0 or = not blank or = blank. The criteria of 0. blank, not blank need to be either combined or seperate depending on the work sheet I am working in. Can you help me out on this one? "Marcelo" wrote: Hi Debi, the 1,0 at the end of the formula means true,false, excel looks for the if question and return 1 if it's true of 0 if it's false, eg assuming that you have "apple" on cell a1, so '=if(a1="apple",true,false) where in your case true = 1 and false = 0 you can also use functions here. to solve your problem I think the best way is use sumproduct '=sumproduct(--(B4:B309="CB")*(Q4:A309=2)) HTH regards from Brazil Marcelo "Debi" escreveu: I have data that I want to count how many rows there are that meet 2 seperate criteria for example I would like to count how many rows contain a CB in column b rows 4 through 309 and a 2 in column q rows 4 through rows 309. When I looked in the help menu it give an example of '=sum(if(b5:b25,"northwind,if(c5:c25-"western",1,0))) first is this the correct formula and second what does the 1,0 represent at the end of the formula example given? "Dav" wrote: You will ahve to give a little more information and an example. What formula? What does the formula refer to, what are the values in the cells Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=562096 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple conditions and multiple return values | Excel Worksheet Functions | |||
COUNT using multiple conditions | Excel Discussion (Misc queries) | |||
How do I set up a formula to count multiple items? | Excel Worksheet Functions | |||
Count with multiple conditions | Excel Worksheet Functions | |||
How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions |