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.

Hi

This should do it:

=SUMPRODUCT(--(C1:C11=1),--(D111="yes"))+SUMPRODUCT(--(C1:C11=1),--(E1:E11="yes"))

Regards,
Per

"Count-Adi" <Count-(E-Mail Removed)> skrev i meddelelsen
news:C2B2570B-8263-4955-B5AB-(E-Mail Removed)...
>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.

{=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.

Try this...

=SUMPRODUCT(--(C2:C15=1),--((D215="yes")+(E2:E15="yes")=2))

--
Biff
Microsoft Excel MVP


"Count-Adi" <Count-(E-Mail Removed)> wrote in message
news:C2B2570B-8263-4955-B5AB-(E-Mail Removed)...
>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.

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.

Great!

Thanks,

Adi

"Per Jessen" wrote:

> Hi
>
> This should do it:
>
> =SUMPRODUCT(--(C1:C11=1),--(D111="yes"))+SUMPRODUCT(--(C1:C11=1),--(E1:E11="yes"))
>
> Regards,
> Per
>
> "Count-Adi" <Count-(E-Mail Removed)> skrev i meddelelsen
> news:C2B2570B-8263-4955-B5AB-(E-Mail Removed)...
> >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.
>
> .
>

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),--(D111="yes"))+SUMPRODUCT(--(C1:C11=1),--(E1:E11="yes"))
>
> Regards,
> Per
>
> "Count-Adi" <Count-(E-Mail Removed)> skrev i meddelelsen
> news:C2B2570B-8263-4955-B5AB-(E-Mail Removed)...
> >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.
>
> .
>

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),--((D215="yes")+(E2:E15="yes")=2))
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Count-Adi" <Count-(E-Mail Removed)> wrote in message
> news:C2B2570B-8263-4955-B5AB-(E-Mail Removed)...
> >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.
>
>
> .
>

>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" <(E-Mail Removed)> wrote in message
news:1ED80AA2-D18E-4A05-AD2D-(E-Mail Removed)...
> 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),--((D215="yes")+(E2:E15="yes")=2))
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Count-Adi" <Count-(E-Mail Removed)> wrote in message
>> news:C2B2570B-8263-4955-B5AB-(E-Mail Removed)...
>> >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.
>>
>>
>> .
>>

try this idea
=SUMPRODUCT((G2:G22=1)*(H2:z22="yes"))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Count-Adi" <Count-(E-Mail Removed)> wrote in message
news:C2B2570B-8263-4955-B5AB-(E-Mail Removed)...
>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.