Conditional countif

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

Andy.

 

[Bob Phillips]

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)

 

[Guest]

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?