COUNTIF or something else?
I am trying to use COUNTIF function where the range might vary. For instance,
I want to know how many yes I have in column D and E together, with the condition that C will be only equal with 1. C D E 1 1 Yes 1 Yes 1 2 Yes 2 2 Yes 3 Yes 3 Yes 3 Yes 3 {=COUNTIF(IF($C$1:$C$11=1,$D$1:$E$11,0),"yes€¯)} should be 2. Thanks for your help. |
COUNTIF or something else?
Hi
This should do it: =SUMPRODUCT(--(C1:C11=1),--(D1:D11="yes"))+SUMPRODUCT(--(C1:C11=1),--(E1:E11="yes")) Regards, Per "Count-Adi" skrev i meddelelsen ... I am trying to use COUNTIF function where the range might vary. For instance, I want to know how many yes I have in column D and E together, with the condition that C will be only equal with 1. C D E 1 1 Yes 1 Yes 1 2 Yes 2 2 Yes 3 Yes 3 Yes 3 Yes 3 {=COUNTIF(IF($C$1:$C$11=1,$D$1:$E$11,0),"yes€¯)} should be 2. Thanks for your help. |
COUNTIF or something else?
{=IF(A2:A12=1,COUNTIF(B2:C11,"Yes"),0)}
This should work Atif "Count-Adi" wrote: I am trying to use COUNTIF function where the range might vary. For instance, I want to know how many yes I have in column D and E together, with the condition that C will be only equal with 1. C D E 1 1 Yes 1 Yes 1 2 Yes 2 2 Yes 3 Yes 3 Yes 3 Yes 3 {=COUNTIF(IF($C$1:$C$11=1,$D$1:$E$11,0),"yes€¯)} should be 2. Thanks for your help. |
COUNTIF or something else?
Try this...
=SUMPRODUCT(--(C2:C15=1),--((D2:D15="yes")+(E2:E15="yes")=2)) -- Biff Microsoft Excel MVP "Count-Adi" wrote in message ... I am trying to use COUNTIF function where the range might vary. For instance, I want to know how many yes I have in column D and E together, with the condition that C will be only equal with 1. C D E 1 1 Yes 1 Yes 1 2 Yes 2 2 Yes 3 Yes 3 Yes 3 Yes 3 {=COUNTIF(IF($C$1:$C$11=1,$D$1:$E$11,0),"yes")} should be 2. Thanks for your help. |
COUNTIF or something else?
In this way it counts for all cells( from B2 to C11), but I am interested
only for those whit 1 in column A. Thanks "Atif" wrote: {=IF(A2:A12=1,COUNTIF(B2:C11,"Yes"),0)} This should work Atif "Count-Adi" wrote: I am trying to use COUNTIF function where the range might vary. For instance, I want to know how many yes I have in column D and E together, with the condition that C will be only equal with 1. C D E 1 1 Yes 1 Yes 1 2 Yes 2 2 Yes 3 Yes 3 Yes 3 Yes 3 {=COUNTIF(IF($C$1:$C$11=1,$D$1:$E$11,0),"yes€¯)} should be 2. Thanks for your help. |
COUNTIF or something else?
Great!
Thanks, Adi "Per Jessen" wrote: Hi This should do it: =SUMPRODUCT(--(C1:C11=1),--(D1:D11="yes"))+SUMPRODUCT(--(C1:C11=1),--(E1:E11="yes")) Regards, Per "Count-Adi" skrev i meddelelsen ... I am trying to use COUNTIF function where the range might vary. For instance, I want to know how many yes I have in column D and E together, with the condition that C will be only equal with 1. C D E 1 1 Yes 1 Yes 1 2 Yes 2 2 Yes 3 Yes 3 Yes 3 Yes 3 {=COUNTIF(IF($C$1:$C$11=1,$D$1:$E$11,0),"yes€¯)} should be 2. Thanks for your help. . |
COUNTIF or something else?
Sorry, I have another question. What about having 30 columns from where I
should count these yes values with the same condition? It will be a long formula. Is there anything else that might be shorter? Thanks "Per Jessen" wrote: Hi This should do it: =SUMPRODUCT(--(C1:C11=1),--(D1:D11="yes"))+SUMPRODUCT(--(C1:C11=1),--(E1:E11="yes")) Regards, Per "Count-Adi" skrev i meddelelsen ... I am trying to use COUNTIF function where the range might vary. For instance, I want to know how many yes I have in column D and E together, with the condition that C will be only equal with 1. C D E 1 1 Yes 1 Yes 1 2 Yes 2 2 Yes 3 Yes 3 Yes 3 Yes 3 {=COUNTIF(IF($C$1:$C$11=1,$D$1:$E$11,0),"yes€¯)} should be 2. Thanks for your help. . |
COUNTIF or something else?
Sorry, It doesn't work. Anything else...(in case that I have more then 2
columns...) Thanks "T. Valko" wrote: Try this... =SUMPRODUCT(--(C2:C15=1),--((D2:D15="yes")+(E2:E15="yes")=2)) -- Biff Microsoft Excel MVP "Count-Adi" wrote in message ... I am trying to use COUNTIF function where the range might vary. For instance, I want to know how many yes I have in column D and E together, with the condition that C will be only equal with 1. C D E 1 1 Yes 1 Yes 1 2 Yes 2 2 Yes 3 Yes 3 Yes 3 Yes 3 {=COUNTIF(IF($C$1:$C$11=1,$D$1:$E$11,0),"yes")} should be 2. Thanks for your help. . |
COUNTIF or something else?
Sorry, It doesn't work.
Hmmm... how many yes I have in column D and E together, with the condition that C will be only equal with 1. ......C.....D.....E 1...2......y......y 2...1.............y 3...1......y.....y 4...3.............. 5...1......y...... If that's your data what result do you expect? My interpretation of your post is: Count how many rows where column C = 1 and *BOTH* column D and column E contain yes. So, based on that interpretation my formula would return1, counting only row 3. Is that what you wanted? If not, a better explanation is needed. (in case that I have more then 2 columns...) Exactly how many columns? -- Biff Microsoft Excel MVP "Count-Adi" wrote in message ... Sorry, It doesn't work. Anything else...(in case that I have more then 2 columns...) Thanks "T. Valko" wrote: Try this... =SUMPRODUCT(--(C2:C15=1),--((D2:D15="yes")+(E2:E15="yes")=2)) -- Biff Microsoft Excel MVP "Count-Adi" wrote in message ... I am trying to use COUNTIF function where the range might vary. For instance, I want to know how many yes I have in column D and E together, with the condition that C will be only equal with 1. C D E 1 1 Yes 1 Yes 1 2 Yes 2 2 Yes 3 Yes 3 Yes 3 Yes 3 {=COUNTIF(IF($C$1:$C$11=1,$D$1:$E$11,0),"yes")} should be 2. Thanks for your help. . |
COUNTIF or something else?
try this idea
=SUMPRODUCT((G2:G22=1)*(H2:z22="yes")) -- Don Guillett Microsoft MVP Excel SalesAid Software "Count-Adi" wrote in message ... I am trying to use COUNTIF function where the range might vary. For instance, I want to know how many yes I have in column D and E together, with the condition that C will be only equal with 1. C D E 1 1 Yes 1 Yes 1 2 Yes 2 2 Yes 3 Yes 3 Yes 3 Yes 3 {=COUNTIF(IF($C$1:$C$11=1,$D$1:$E$11,0),"yes€¯)} should be 2. Thanks for your help. |
All times are GMT +1. The time now is 07:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com