Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. . |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. . |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. . |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. . |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CountIF | Excel Discussion (Misc queries) | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |