Conditional countif

Hi

Try something like:
=SUMPRODUCT((A1:A50="apples")*(B1:B50="oranges"))
I don't quite understand the "If this occurs on multiple rows, I want to
count them all as one sum." bit, but it's a start at least!

Andy.

"force530" <[email protected]> wrote in message
news:[email protected]...
>I have two columns with lets say a range of 50 (a1:a50) and (B1:B50). I
>want
> to count the number of occurences when a specific arguement occurs. i.e.,
> If
> A23=apples and b23=oranges, I want to count this occurence, but only if
> this
> occurs together on the same row. If this occurs on multiple rows, I want
> to
> count them all as one sum.

if you can't find a neater way, you could always add a third column with
=IF(AND(B1="oranges",A1="apples"),1,0)
or simply
=AND(B1="oranges",A1="apples"),

and count the occurences of "1" (or "TRUE" for 2nd formula) in this column.
This column could then be hidden for aesthetics.
if i find a better way i'll let you know

----
kirsty

"force530" wrote:

> I have two columns with lets say a range of 50 (a1:a50) and (B1:B50). I want
> to count the number of occurences when a specific arguement occurs. i.e., If
> A23=apples and b23=oranges, I want to count this occurence, but only if this
> occurs together on the same row. If this occurs on multiple rows, I want to
> count them all as one sum.

Thanks ndy .. it works. What if I wanted to add grapes in column A along with
Apples?

"Andy" wrote:

> Hi
>
> Try something like:
> =SUMPRODUCT((A1:A50="apples")*(B1:B50="oranges"))
> I don't quite understand the "If this occurs on multiple rows, I want to
> count them all as one sum." bit, but it's a start at least!
>
> Andy.
>
> "force530" <[email protected]> wrote in message
> news:[email protected]...
> >I have two columns with lets say a range of 50 (a1:a50) and (B1:B50). I
> >want
> > to count the number of occurences when a specific arguement occurs. i.e.,
> > If
> > A23=apples and b23=oranges, I want to count this occurence, but only if
> > this
> > occurs together on the same row. If this occurs on multiple rows, I want
> > to
> > count them all as one sum.
>
>
>

Try this:
=SUMPRODUCT(OR((A1:A50="apples"),(A1:A50="grapes"))*(B1:B50="oranges"))

Andy.


"force530" <[email protected]> wrote in message
news:[email protected]...
> Thanks ndy .. it works. What if I wanted to add grapes in column A along
> with
> Apples?
>
> "Andy" wrote:
>
>> Hi
>>
>> Try something like:
>> =SUMPRODUCT((A1:A50="apples")*(B1:B50="oranges"))
>> I don't quite understand the "If this occurs on multiple rows, I want to
>> count them all as one sum." bit, but it's a start at least!
>>
>> Andy.
>>
>> "force530" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have two columns with lets say a range of 50 (a1:a50) and (B1:B50). I
>> >want
>> > to count the number of occurences when a specific arguement occurs.
>> > i.e.,
>> > If
>> > A23=apples and b23=oranges, I want to count this occurence, but only if
>> > this
>> > occurs together on the same row. If this occurs on multiple rows, I
>> > want
>> > to
>> > count them all as one sum.
>>
>>
>>

Careful Andy,

OR((A1:A50="apples"),(A1:A50="grapes")) will evaluate to true if there is
even one occurrence of apples or grapes in column A, and thus will count all
occurrences of oranges in B. So Jim in A, oranges in B will count. Don't
think this is what is wanted.

Try instead.

=SUMPRODUCT(((A1:A10="apples")+(A1:A10="grapes"))*(B1:B10="oranges"))

or

=SUMPRODUCT(--((A1:A10="apples")+(A1:A10="grapes")),--(B1:B10="oranges"))

in my preferred style<vbg>,

or most succinctly, and my preferred solution

=SUMPRODUCT(((A1:A10={"apples","grapes"}))*(B1:B10="oranges"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


<Andy> wrote in message news:[email protected]...
> Try this:
> =SUMPRODUCT(OR((A1:A50="apples"),(A1:A50="grapes"))*(B1:B50="oranges"))
>
> Andy.
>
>
> "force530" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks ndy .. it works. What if I wanted to add grapes in column A along
> > with
> > Apples?
> >
> > "Andy" wrote:
> >
> >> Hi
> >>
> >> Try something like:
> >> =SUMPRODUCT((A1:A50="apples")*(B1:B50="oranges"))
> >> I don't quite understand the "If this occurs on multiple rows, I want
to
> >> count them all as one sum." bit, but it's a start at least!
> >>
> >> Andy.
> >>
> >> "force530" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I have two columns with lets say a range of 50 (a1:a50) and (B1:B50).
I
> >> >want
> >> > to count the number of occurences when a specific arguement occurs.
> >> > i.e.,
> >> > If
> >> > A23=apples and b23=oranges, I want to count this occurence, but only
if
> >> > this
> >> > occurs together on the same row. If this occurs on multiple rows, I
> >> > want
> >> > to
> >> > count them all as one sum.
> >>
> >>
> >>
>
>

It appears all of the solutions work ... thanks. How would I add multiple
aguements in column B, the same column as oranges, i.e., pears, melons, etc?

"Bob Phillips" wrote:

> Careful Andy,
>
> OR((A1:A50="apples"),(A1:A50="grapes")) will evaluate to true if there is
> even one occurrence of apples or grapes in column A, and thus will count all
> occurrences of oranges in B. So Jim in A, oranges in B will count. Don't
> think this is what is wanted.
>
> Try instead.
>
> =SUMPRODUCT(((A1:A10="apples")+(A1:A10="grapes"))*(B1:B10="oranges"))
>
> or
>
> =SUMPRODUCT(--((A1:A10="apples")+(A1:A10="grapes")),--(B1:B10="oranges"))
>
> in my preferred style<vbg>,
>
> or most succinctly, and my preferred solution
>
> =SUMPRODUCT(((A1:A10={"apples","grapes"}))*(B1:B10="oranges"))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> <Andy> wrote in message news:[email protected]...
> > Try this:
> > =SUMPRODUCT(OR((A1:A50="apples"),(A1:A50="grapes"))*(B1:B50="oranges"))
> >
> > Andy.
> >
> >
> > "force530" <[email protected]> wrote in message
> > news:[email protected]...
> > > Thanks ndy .. it works. What if I wanted to add grapes in column A along
> > > with
> > > Apples?
> > >
> > > "Andy" wrote:
> > >
> > >> Hi
> > >>
> > >> Try something like:
> > >> =SUMPRODUCT((A1:A50="apples")*(B1:B50="oranges"))
> > >> I don't quite understand the "If this occurs on multiple rows, I want
> to
> > >> count them all as one sum." bit, but it's a start at least!
> > >>
> > >> Andy.
> > >>
> > >> "force530" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> >I have two columns with lets say a range of 50 (a1:a50) and (B1:B50).
> I
> > >> >want
> > >> > to count the number of occurences when a specific arguement occurs.
> > >> > i.e.,
> > >> > If
> > >> > A23=apples and b23=oranges, I want to count this occurence, but only
> if
> > >> > this
> > >> > occurs together on the same row. If this occurs on multiple rows, I
> > >> > want
> > >> > to
> > >> > count them all as one sum.
> > >>
> > >>
> > >>
> >
> >
>
>
>

I have two columns with lets say a range of 50 (a1:a50) and (B1:B50). I want
to count the number of occurences when a specific arguement occurs. i.e., If
A23=apples and b23=oranges, I want to count this occurence, but only if this
occurs together on the same row. If this occurs on multiple rows, I want to
count them all as one sum.

Hi

Try something like:
=SUMPRODUCT((A1:A50="apples")*(B1:B50="oranges"))
I don't quite understand the "If this occurs on multiple rows, I want to
count them all as one sum." bit, but it's a start at least!

Andy.

"force530" <[email protected]> wrote in message
news:[email protected]...
>I have two columns with lets say a range of 50 (a1:a50) and (B1:B50). I
>want
> to count the number of occurences when a specific arguement occurs. i.e.,
> If
> A23=apples and b23=oranges, I want to count this occurence, but only if
> this
> occurs together on the same row. If this occurs on multiple rows, I want
> to
> count them all as one sum.

if you can't find a neater way, you could always add a third column with
=IF(AND(B1="oranges",A1="apples"),1,0)
or simply
=AND(B1="oranges",A1="apples"),

and count the occurences of "1" (or "TRUE" for 2nd formula) in this column.
This column could then be hidden for aesthetics.
if i find a better way i'll let you know

----
kirsty

"force530" wrote:

> I have two columns with lets say a range of 50 (a1:a50) and (B1:B50). I want
> to count the number of occurences when a specific arguement occurs. i.e., If
> A23=apples and b23=oranges, I want to count this occurence, but only if this
> occurs together on the same row. If this occurs on multiple rows, I want to
> count them all as one sum.

Thanks ndy .. it works. What if I wanted to add grapes in column A along with
Apples?

"Andy" wrote:

> Hi
>
> Try something like:
> =SUMPRODUCT((A1:A50="apples")*(B1:B50="oranges"))
> I don't quite understand the "If this occurs on multiple rows, I want to
> count them all as one sum." bit, but it's a start at least!
>
> Andy.
>
> "force530" <[email protected]> wrote in message
> news:[email protected]...
> >I have two columns with lets say a range of 50 (a1:a50) and (B1:B50). I
> >want
> > to count the number of occurences when a specific arguement occurs. i.e.,
> > If
> > A23=apples and b23=oranges, I want to count this occurence, but only if
> > this
> > occurs together on the same row. If this occurs on multiple rows, I want
> > to
> > count them all as one sum.
>
>
>

Try this:
=SUMPRODUCT(OR((A1:A50="apples"),(A1:A50="grapes"))*(B1:B50="oranges"))

Andy.


"force530" <[email protected]> wrote in message
news:[email protected]...
> Thanks ndy .. it works. What if I wanted to add grapes in column A along
> with
> Apples?
>
> "Andy" wrote:
>
>> Hi
>>
>> Try something like:
>> =SUMPRODUCT((A1:A50="apples")*(B1:B50="oranges"))
>> I don't quite understand the "If this occurs on multiple rows, I want to
>> count them all as one sum." bit, but it's a start at least!
>>
>> Andy.
>>
>> "force530" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have two columns with lets say a range of 50 (a1:a50) and (B1:B50). I
>> >want
>> > to count the number of occurences when a specific arguement occurs.
>> > i.e.,
>> > If
>> > A23=apples and b23=oranges, I want to count this occurence, but only if
>> > this
>> > occurs together on the same row. If this occurs on multiple rows, I
>> > want
>> > to
>> > count them all as one sum.
>>
>>
>>

Careful Andy,

OR((A1:A50="apples"),(A1:A50="grapes")) will evaluate to true if there is
even one occurrence of apples or grapes in column A, and thus will count all
occurrences of oranges in B. So Jim in A, oranges in B will count. Don't
think this is what is wanted.

Try instead.

=SUMPRODUCT(((A1:A10="apples")+(A1:A10="grapes"))*(B1:B10="oranges"))

or

=SUMPRODUCT(--((A1:A10="apples")+(A1:A10="grapes")),--(B1:B10="oranges"))

in my preferred style<vbg>,

or most succinctly, and my preferred solution

=SUMPRODUCT(((A1:A10={"apples","grapes"}))*(B1:B10="oranges"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


<Andy> wrote in message news:[email protected]...
> Try this:
> =SUMPRODUCT(OR((A1:A50="apples"),(A1:A50="grapes"))*(B1:B50="oranges"))
>
> Andy.
>
>
> "force530" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks ndy .. it works. What if I wanted to add grapes in column A along
> > with
> > Apples?
> >
> > "Andy" wrote:
> >
> >> Hi
> >>
> >> Try something like:
> >> =SUMPRODUCT((A1:A50="apples")*(B1:B50="oranges"))
> >> I don't quite understand the "If this occurs on multiple rows, I want
to
> >> count them all as one sum." bit, but it's a start at least!
> >>
> >> Andy.
> >>
> >> "force530" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I have two columns with lets say a range of 50 (a1:a50) and (B1:B50).
I
> >> >want
> >> > to count the number of occurences when a specific arguement occurs.
> >> > i.e.,
> >> > If
> >> > A23=apples and b23=oranges, I want to count this occurence, but only
if
> >> > this
> >> > occurs together on the same row. If this occurs on multiple rows, I
> >> > want
> >> > to
> >> > count them all as one sum.
> >>
> >>
> >>
>
>

It appears all of the solutions work ... thanks. How would I add multiple
aguements in column B, the same column as oranges, i.e., pears, melons, etc?

"Bob Phillips" wrote:

> Careful Andy,
>
> OR((A1:A50="apples"),(A1:A50="grapes")) will evaluate to true if there is
> even one occurrence of apples or grapes in column A, and thus will count all
> occurrences of oranges in B. So Jim in A, oranges in B will count. Don't
> think this is what is wanted.
>
> Try instead.
>
> =SUMPRODUCT(((A1:A10="apples")+(A1:A10="grapes"))*(B1:B10="oranges"))
>
> or
>
> =SUMPRODUCT(--((A1:A10="apples")+(A1:A10="grapes")),--(B1:B10="oranges"))
>
> in my preferred style<vbg>,
>
> or most succinctly, and my preferred solution
>
> =SUMPRODUCT(((A1:A10={"apples","grapes"}))*(B1:B10="oranges"))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> <Andy> wrote in message news:[email protected]...
> > Try this:
> > =SUMPRODUCT(OR((A1:A50="apples"),(A1:A50="grapes"))*(B1:B50="oranges"))
> >
> > Andy.
> >
> >
> > "force530" <[email protected]> wrote in message
> > news:[email protected]...
> > > Thanks ndy .. it works. What if I wanted to add grapes in column A along
> > > with
> > > Apples?
> > >
> > > "Andy" wrote:
> > >
> > >> Hi
> > >>
> > >> Try something like:
> > >> =SUMPRODUCT((A1:A50="apples")*(B1:B50="oranges"))
> > >> I don't quite understand the "If this occurs on multiple rows, I want
> to
> > >> count them all as one sum." bit, but it's a start at least!
> > >>
> > >> Andy.
> > >>
> > >> "force530" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> >I have two columns with lets say a range of 50 (a1:a50) and (B1:B50).
> I
> > >> >want
> > >> > to count the number of occurences when a specific arguement occurs.
> > >> > i.e.,
> > >> > If
> > >> > A23=apples and b23=oranges, I want to count this occurence, but only
> if
> > >> > this
> > >> > occurs together on the same row. If this occurs on multiple rows, I
> > >> > want
> > >> > to
> > >> > count them all as one sum.
> > >>
> > >>
> > >>
> >
> >
>
>
>