Counting Across Multiple Ranges, Based on Condition
 

Hey all! I hope you guys can provide some insight to this. I have a project
that's due on Friday morning, but can't seem to get this formula figured out.
Here's what I'm workin on:

I have a spreadsheet that contains alot of information around which
countries own a particular application. From that, I have done a COUNTIF
function to show a count on how many applications per country. Now I need to
know, based on the number of applications per country, how many of those meet
a certain condition? Basically, here's how my spreadsheet is laid out:

Column F contains the Country Name (FAP-Malaysia - however, in my formulas I
have been using "*Malaysia"). Column J contains the condition I need to be
met, which is a Yes or No. I need to know how many of the applications for
Malaysia are Yes for this condition.

Can anyone help?

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("Malaysia",F1:F100))),--(J1:J100="Yes"))

OR

A1 = Malaysia
B1 = Yes

=SUMPRODUCT(--(ISNUMBER(SEARCH(A1,F1:F100))),--(J1:J100=B1))

Biff

"Stacy" wrote in message
...
Hey all! I hope you guys can provide some insight to this. I have a
project
that's due on Friday morning, but can't seem to get this formula figured
out.
Here's what I'm workin on:

I have a spreadsheet that contains alot of information around which
countries own a particular application. From that, I have done a COUNTIF
function to show a count on how many applications per country. Now I need
to
know, based on the number of applications per country, how many of those
meet
a certain condition? Basically, here's how my spreadsheet is laid out:

Column F contains the Country Name (FAP-Malaysia - however, in my formulas
I
have been using "*Malaysia"). Column J contains the condition I need to
be
met, which is a Yes or No. I need to know how many of the applications
for
Malaysia are Yes for this condition.

Can anyone help?

Thanks, I think this worked. I'm just trying to validate right now.

"Biff" wrote:

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("Malaysia",F1:F100))),--(J1:J100="Yes"))

OR

A1 = Malaysia
B1 = Yes

=SUMPRODUCT(--(ISNUMBER(SEARCH(A1,F1:F100))),--(J1:J100=B1))

Biff

"Stacy" wrote in message
...
Hey all! I hope you guys can provide some insight to this. I have a
project
that's due on Friday morning, but can't seem to get this formula figured
out.
Here's what I'm workin on:

I have a spreadsheet that contains alot of information around which
countries own a particular application. From that, I have done a COUNTIF
function to show a count on how many applications per country. Now I need
to
know, based on the number of applications per country, how many of those
meet
a certain condition? Basically, here's how my spreadsheet is laid out:

Column F contains the Country Name (FAP-Malaysia - however, in my formulas
I
have been using "*Malaysia"). Column J contains the condition I need to
be
met, which is a Yes or No. I need to know how many of the applications
for
Malaysia are Yes for this condition.

Can anyone help?