I need a formula which calculates the number of occurences of a variable
apprearing in a column if the entry in another row is equal to N or n.

I have attempted to use the below but it shows a VALUE error message:

={countif(if(A1:A10="N",C1:C10),24)}

24 being the variable to be counted and C1:C10 is the range where the
variable is listed.

Your description is a little unclear to me. Does this formula do what you
want?

=SUMPRODUCT((A1:A10="N")*(C1:C10=24))

Rick


"PD" <(E-Mail Removed)> wrote in message
news:ECD098AF-1406-4075-BA71-(E-Mail Removed)...
>I need a formula which calculates the number of occurences of a variable
> apprearing in a column if the entry in another row is equal to N or n.
>
> I have attempted to use the below but it shows a VALUE error message:
>
> ={countif(if(A1:A10="N",C1:C10),24)}
>
> 24 being the variable to be counted and C1:C10 is the range where the
> variable is listed.

Hi Rick,

Unfortunately, the below isn't giving the correct answer.

To help understand the query, the file has four columns which has the name
of the staff, customer details, Y/N and reason code for analysis. We require
the count of the specified reason codes only if the other column has a
reading of N.




"Rick Rothstein (MVP - VB)" wrote:

> Your description is a little unclear to me. Does this formula do what you
> want?
>
> =SUMPRODUCT((A1:A10="N")*(C1:C10=24))
>
> Rick
>
>
> "PD" <(E-Mail Removed)> wrote in message
> news:ECD098AF-1406-4075-BA71-(E-Mail Removed)...
> >I need a formula which calculates the number of occurences of a variable
> > apprearing in a column if the entry in another row is equal to N or n.
> >
> > I have attempted to use the below but it shows a VALUE error message:
> >
> > ={countif(if(A1:A10="N",C1:C10),24)}
> >
> > 24 being the variable to be counted and C1:C10 is the range where the
> > variable is listed.
>
>

With the possible exception of the wrong columns being specified (I used the
columns you showed in your posting), I do not see why the formula I posted
does not do what you want. Can you show us maybe 6 lines of data from your
worksheet and then answer you expect the formula to give for the data you
post?

Rick


"PD" <(E-Mail Removed)> wrote in message
news:C157C529-7CFC-45B0-BCDE-(E-Mail Removed)...
> Hi Rick,
>
> Unfortunately, the below isn't giving the correct answer.
>
> To help understand the query, the file has four columns which has the name
> of the staff, customer details, Y/N and reason code for analysis. We
> require
> the count of the specified reason codes only if the other column has a
> reading of N.
>
>
>
>
> "Rick Rothstein (MVP - VB)" wrote:
>
>> Your description is a little unclear to me. Does this formula do what you
>> want?
>>
>> =SUMPRODUCT((A1:A10="N")*(C1:C10=24))
>>
>> Rick
>>
>>
>> "PD" <(E-Mail Removed)> wrote in message
>> news:ECD098AF-1406-4075-BA71-(E-Mail Removed)...
>> >I need a formula which calculates the number of occurences of a variable
>> > apprearing in a column if the entry in another row is equal to N or n.
>> >
>> > I have attempted to use the below but it shows a VALUE error message:
>> >
>> > ={countif(if(A1:A10="N",C1:C10),24)}
>> >
>> > 24 being the variable to be counted and C1:C10 is the range where the
>> > variable is listed.
>>
>>