Conditional countif
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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 ...
Try this:
=SUMPRODUCT(OR((A1:A50="apples"),(A1:A50="grapes") )*(B1:B50="oranges"))

Andy.


"force530" wrote in message
...
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" wrote in message
...
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 ...
Try this:
=SUMPRODUCT(OR((A1:A50="apples"),(A1:A50="grapes") )*(B1:B50="oranges"))

Andy.


"force530" wrote in message
...
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" wrote in message
...
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.