If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
CONDITIONAL COUNTIF FUNTIONS PLEASE HELP!!!
I am trying to count a certain number of cells based on whether a condition
is satisfied. I want a formular rather like the sumproduct but countproduct instead. Please see example below. I want to count for both companies COMP1 and COMP2 how many non zero cells there are in the first table, but only when the corresponding cell in the 2nd table has a "y" in that cell. at the moment I can do this using if functions with a countif function in the middle. However I am doing this over a period of 3 years so it makes my formular ridiculously long. A B C 1 JAN FEB 2 COMP 1 1 2 3 COMP 2 1 0 4 5 JAN FEB 6 COMP 1 y 7 COMP 2 y y 8 9 count none zero with "y" 10 COMP 1 1 -- =IF(B6="y",COUNTIF(B2,"0"),0)+IF(C6="y",COUNTIF( C2,"0"),0) 11 COMP 2 1 -- =IF(B7="y",COUNTIF(B7,"0"),0)+IF(C7="y",COUNTIF( C7,"0"),0) I want a way to do it for example = countproduct(b2:c2,(b6:c6="y")*1)) Can anyone help me?? is there another way to do this? Laurence |
#2
|
|||
|
|||
CONDITIONAL COUNTIF FUNTIONS PLEASE HELP!!!
You're pretty close. You can still use SUMPRODUCT, just force all the array
to become true/false conditions. Comp 1: =SUMPRODUCT((B2:C20)*(B6:C6="y")) Comp 2: =SUMPRODUCT((B3:C30)*(B7:C7="y")) Increase array sizes as desired. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "laandmc" wrote: I am trying to count a certain number of cells based on whether a condition is satisfied. I want a formular rather like the sumproduct but countproduct instead. Please see example below. I want to count for both companies COMP1 and COMP2 how many non zero cells there are in the first table, but only when the corresponding cell in the 2nd table has a "y" in that cell. at the moment I can do this using if functions with a countif function in the middle. However I am doing this over a period of 3 years so it makes my formular ridiculously long. A B C 1 JAN FEB 2 COMP 1 1 2 3 COMP 2 1 0 4 5 JAN FEB 6 COMP 1 y 7 COMP 2 y y 8 9 count none zero with "y" 10 COMP 1 1 -- =IF(B6="y",COUNTIF(B2,"0"),0)+IF(C6="y",COUNTIF( C2,"0"),0) 11 COMP 2 1 -- =IF(B7="y",COUNTIF(B7,"0"),0)+IF(C7="y",COUNTIF( C7,"0"),0) I want a way to do it for example = countproduct(b2:c2,(b6:c6="y")*1)) Can anyone help me?? is there another way to do this? Laurence |
#3
|
|||
|
|||
CONDITIONAL COUNTIF FUNTIONS PLEASE HELP!!!
=SUMPRODUCT(--(A1:A3=A10),--(B1:B30)*--(B5:B7="y"))+SUMPRODUCT(--(A1:A3=A10),--(C1:C30)*--(C5:C7="y"))
Will get you a count for company 1 same formula but change A10 to A11 for company 2. Merely $ the rest of the formulas as needed to keep your ranges constant. "laandmc" wrote: I am trying to count a certain number of cells based on whether a condition is satisfied. I want a formular rather like the sumproduct but countproduct instead. Please see example below. I want to count for both companies COMP1 and COMP2 how many non zero cells there are in the first table, but only when the corresponding cell in the 2nd table has a "y" in that cell. at the moment I can do this using if functions with a countif function in the middle. However I am doing this over a period of 3 years so it makes my formular ridiculously long. A B C 1 JAN FEB 2 COMP 1 1 2 3 COMP 2 1 0 4 5 JAN FEB 6 COMP 1 y 7 COMP 2 y y 8 9 count none zero with "y" 10 COMP 1 1 -- =IF(B6="y",COUNTIF(B2,"0"),0)+IF(C6="y",COUNTIF( C2,"0"),0) 11 COMP 2 1 -- =IF(B7="y",COUNTIF(B7,"0"),0)+IF(C7="y",COUNTIF( C7,"0"),0) I want a way to do it for example = countproduct(b2:c2,(b6:c6="y")*1)) Can anyone help me?? is there another way to do this? Laurence |
#4
|
|||
|
|||
CONDITIONAL COUNTIF FUNTIONS PLEASE HELP!!!
With a multiplication * you don't need the double unary minus.
--(B1:B30)*--(B5:B7="y") is the same as (B1:B30)*(B5:B7="y") The double unary minus is there to force an arithmetic operation where you weren't already doing one, but in your case you are doing the multiplication. -- David Biddulph "Sean Timmons" wrote in message ... =SUMPRODUCT(--(A1:A3=A10),--(B1:B30)*--(B5:B7="y"))+SUMPRODUCT(--(A1:A3=A10),--(C1:C30)*--(C5:C7="y")) Will get you a count for company 1 same formula but change A10 to A11 for company 2. Merely $ the rest of the formulas as needed to keep your ranges constant. "laandmc" wrote: I am trying to count a certain number of cells based on whether a condition is satisfied. I want a formular rather like the sumproduct but countproduct instead. Please see example below. I want to count for both companies COMP1 and COMP2 how many non zero cells there are in the first table, but only when the corresponding cell in the 2nd table has a "y" in that cell. at the moment I can do this using if functions with a countif function in the middle. However I am doing this over a period of 3 years so it makes my formular ridiculously long. A B C 1 JAN FEB 2 COMP 1 1 2 3 COMP 2 1 0 4 5 JAN FEB 6 COMP 1 y 7 COMP 2 y y 8 9 count none zero with "y" 10 COMP 1 1 -- =IF(B6="y",COUNTIF(B2,"0"),0)+IF(C6="y",COUNTIF( C2,"0"),0) 11 COMP 2 1 -- =IF(B7="y",COUNTIF(B7,"0"),0)+IF(C7="y",COUNTIF( C7,"0"),0) I want a way to do it for example = countproduct(b2:c2,(b6:c6="y")*1)) Can anyone help me?? is there another way to do this? Laurence |
#5
|
|||
|
|||
CONDITIONAL COUNTIF FUNTIONS PLEASE HELP!!!
Thank you for the correction. Good to know!
"David Biddulph" wrote: With a multiplication * you don't need the double unary minus. --(B1:B30)*--(B5:B7="y") is the same as (B1:B30)*(B5:B7="y") The double unary minus is there to force an arithmetic operation where you weren't already doing one, but in your case you are doing the multiplication. -- David Biddulph "Sean Timmons" wrote in message ... =SUMPRODUCT(--(A1:A3=A10),--(B1:B30)*--(B5:B7="y"))+SUMPRODUCT(--(A1:A3=A10),--(C1:C30)*--(C5:C7="y")) Will get you a count for company 1 same formula but change A10 to A11 for company 2. Merely $ the rest of the formulas as needed to keep your ranges constant. "laandmc" wrote: I am trying to count a certain number of cells based on whether a condition is satisfied. I want a formular rather like the sumproduct but countproduct instead. Please see example below. I want to count for both companies COMP1 and COMP2 how many non zero cells there are in the first table, but only when the corresponding cell in the 2nd table has a "y" in that cell. at the moment I can do this using if functions with a countif function in the middle. However I am doing this over a period of 3 years so it makes my formular ridiculously long. A B C 1 JAN FEB 2 COMP 1 1 2 3 COMP 2 1 0 4 5 JAN FEB 6 COMP 1 y 7 COMP 2 y y 8 9 count none zero with "y" 10 COMP 1 1 -- =IF(B6="y",COUNTIF(B2,"0"),0)+IF(C6="y",COUNTIF( C2,"0"),0) 11 COMP 2 1 -- =IF(B7="y",COUNTIF(B7,"0"),0)+IF(C7="y",COUNTIF( C7,"0"),0) I want a way to do it for example = countproduct(b2:c2,(b6:c6="y")*1)) Can anyone help me?? is there another way to do this? Laurence |
Thread Tools | |
Display Modes | |
|
|