ExcelBanter - Conditional Countif

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Conditional Countif (https://www.excelbanter.com/excel-worksheet-functions/33494-conditional-countif.html)

Conditional Countif

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.

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

--
HTH

Bob Phillips

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

This one doesnt work properly ...These are the conditions; If Column A has
either apples and/or grapes I would like them to be counted only if oranges
or pears are in the same row in column B. i.e., A5=apples or grapes and B5=
oranges or pears, then the condition is met and it should reflect 1.

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

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

force530 wrote:
[...]
These are the conditions; If Column A has
either apples and/or grapes I would like them to be counted only if oranges
or pears are in the same row in column B. i.e., A5=apples or grapes and B5=
oranges or pears, then the condition is met and it should reflect 1.
[...]

=SUMPRODUCT(--ISNUMBER(MATCH($A$2:$A$10,{"apples","grapes"},0)),--ISNUMBER(MATCH($B$2:$B$10,{"oranges","pears"},0)))

Thanks for the reply ...

Okay here is the actual formula ... It will not add the "Filed-Arrest
Felony" when entered. The formula is accepted, but wont add the last part.

=SUMPRODUCT(--ISNUMBER(MATCH('OFFENSE LOG'!$F$7:$F$57,{"Murder","Capital
Murder"},0)),--ISNUMBER(MATCH('OFFENSE LOG'!$P$7:$P$57,{"filed-at large
felony","filed-arrest felony"})))

"Aladin Akyurek" wrote:

force530 wrote:
[...]
These are the conditions; If Column A has
either apples and/or grapes I would like them to be counted only if oranges
or pears are in the same row in column B. i.e., A5=apples or grapes and B5=
oranges or pears, then the condition is met and it should reflect 1.
[...]

=SUMPRODUCT(--ISNUMBER(MATCH($A$2:$A$10,{"apples","grapes"},0)),--ISNUMBER(MATCH($B$2:$B$10,{"oranges","pears"},0)))

It just doest seem to recognize the "filed-arrest felony"

"force530" wrote:

Thanks for the reply ...

Okay here is the actual formula ... It will not add the "Filed-Arrest
Felony" when entered. The formula is accepted, but wont add the last part.

=SUMPRODUCT(--ISNUMBER(MATCH('OFFENSE LOG'!$F$7:$F$57,{"Murder","Capital
Murder"},0)),--ISNUMBER(MATCH('OFFENSE LOG'!$P$7:$P$57,{"filed-at large
felony","filed-arrest felony"})))

"Aladin Akyurek" wrote:

force530 wrote:
[...]
These are the conditions; If Column A has
either apples and/or grapes I would like them to be counted only if oranges
or pears are in the same row in column B. i.e., A5=apples or grapes and B5=
oranges or pears, then the condition is met and it should reflect 1.
[...]

=SUMPRODUCT(--ISNUMBER(MATCH($A$2:$A$10,{"apples","grapes"},0)),--ISNUMBER(MATCH($B$2:$B$10,{"oranges","pears"},0)))

You need 0 in the second MATCH too...

=SUMPRODUCT(--ISNUMBER(MATCH('OFFENSE LOG'!$F$7:$F$57,{"Murder","Capital
Murder"},0)),--ISNUMBER(MATCH('OFFENSE LOG'!$P$7:$P$57,{"filed-at large
felony","filed-arrest felony"},0)))

force530 wrote:
It just doest seem to recognize the "filed-arrest felony"

"force530" wrote:

Thanks for the reply ...

Okay here is the actual formula ... It will not add the "Filed-Arrest
Felony" when entered. The formula is accepted, but wont add the last part.

=SUMPRODUCT(--ISNUMBER(MATCH('OFFENSE LOG'!$F$7:$F$57,{"Murder","Capital
Murder"},0)),--ISNUMBER(MATCH('OFFENSE LOG'!$P$7:$P$57,{"filed-at large
felony","filed-arrest felony"})))

"Aladin Akyurek" wrote:

force530 wrote:
[...]

These are the conditions; If Column A has
either apples and/or grapes I would like them to be counted only if oranges
or pears are in the same row in column B. i.e., A5=apples or grapes and B5=
oranges or pears, then the condition is met and it should reflect 1.

[...]

=SUMPRODUCT(--ISNUMBER(MATCH($A$2:$A$10,{"apples","grapes"},0)),--ISNUMBER(MATCH($B$2:$B$10,{"oranges","pears"},0)))

Works great ..... Thanks!

"Aladin Akyurek" wrote:

You need 0 in the second MATCH too...

=SUMPRODUCT(--ISNUMBER(MATCH('OFFENSE LOG'!$F$7:$F$57,{"Murder","Capital
Murder"},0)),--ISNUMBER(MATCH('OFFENSE LOG'!$P$7:$P$57,{"filed-at large
felony","filed-arrest felony"},0)))

force530 wrote:
It just doest seem to recognize the "filed-arrest felony"

"force530" wrote:

Thanks for the reply ...

Okay here is the actual formula ... It will not add the "Filed-Arrest
Felony" when entered. The formula is accepted, but wont add the last part.

=SUMPRODUCT(--ISNUMBER(MATCH('OFFENSE LOG'!$F$7:$F$57,{"Murder","Capital
Murder"},0)),--ISNUMBER(MATCH('OFFENSE LOG'!$P$7:$P$57,{"filed-at large
felony","filed-arrest felony"})))

"Aladin Akyurek" wrote:

force530 wrote:
[...]

These are the conditions; If Column A has
either apples and/or grapes I would like them to be counted only if oranges
or pears are in the same row in column B. i.e., A5=apples or grapes and B5=
oranges or pears, then the condition is met and it should reflect 1.

[...]

=SUMPRODUCT(--ISNUMBER(MATCH($A$2:$A$10,{"apples","grapes"},0)),--ISNUMBER(MATCH($B$2:$B$10,{"oranges","pears"},0)))