Help with Formulas
 

I need help with creating a formula. I want to count the total number
of individuals who meet a certain criteria in one column and a certain
criteria in another column. For example: I want to find out the total
number of people who live in Germany and went on a specific trip. For
argument sake let's say column A is at lists only Y or N for yes and
no, and column B is a list of Countries such as Germany, America, etc.
I would like to figure out the total number of individuals who have a Y
in column A and the word Germany in column B. This is what I have done
so far and it works for counting only those with Germany in column B or
a Y in column A:

=COUNTIF('[Spreadsheet Name.xls]'!$B:$B,"Germany") or
=COUNTIF('[Spreadsheet Name.xls]'!$A:$A,"Y")

The first formula will give me the total number of individuals with the
word Germany in column B, and the second will give me a total number of
individuals with a Y in column A. How can I combine these two formulas
to come up with one that will only count those individuals who have both
the word Germany in column B and a Y in column A? I appreciate your
assistance with this matter. Please respond to

Thanks,
Fritz


--
fritzj8
------------------------------------------------------------------------
fritzj8's Profile: http://www.excelforum.com/member.php...o&userid=27167
View this thread: http://www.excelforum.com/showthread...hreadid=466772

=SUMPRODUCT(--(A1:A100=\"Y\"),--(B1:B100=\"GERMANY\"))

is your formula and next time, it is not necessary to multipost
whatever problem you have.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=466772

fritzj8 Wrote:
I need help with creating a formula. I want to count the total number
of individuals who meet a certain criteria in one column and a certain
criteria in another column. For example: I want to find out the total
number of people who live in Germany and went on a specific trip. For
argument sake let's say column A is at lists only Y or N for yes and
no, and column B is a list of Countries such as Germany, America, etc.
I would like to figure out the total number of individuals who have a Y
in column A and the word Germany in column B. This is what I have done
so far and it works for counting only those with Germany in column B or
a Y in column A:

=COUNTIF('[Spreadsheet Name.xls]'!$B:$B,"Germany") or
=COUNTIF('[Spreadsheet Name.xls]'!$A:$A,"Y")

The first formula will give me the total number of individuals with the
word Germany in column B, and the second will give me a total number of
individuals with a Y in column A. How can I combine these two formulas
to come up with one that will only count those individuals who have both
the word Germany in column B and a Y in column A? I appreciate your
assistance with this matter. Please respond to

Thanks,
Fritz

Hi Fritz

Try this =SUM((A1:A15="Y")*(B1:B15="Germany"))

This needs to be enterred as an array, so type in the formula and then
press CTRL/Shift/Enter all together

Change the ranges to suit your data


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=466772

Thanks BenjieLop that did it. Now all I need help with figuring out is
how to count only those fields that have a date in them. For example:
Column C has various dates listed and some of the entries are just text
such as 12-Sep-05 or the words Did Not Go. However, all I need to count
are only those fields with a date in the block and not text. Any ideas?


--
fritzj8
------------------------------------------------------------------------
fritzj8's Profile: http://www.excelforum.com/member.php...o&userid=27167
View this thread: http://www.excelforum.com/showthread...hreadid=466772

fritzj8 Wrote:
Thanks BenjieLop that did it. Now all I need help with figuring out is
how to count only those fields that have a date in them. For example:
Column C has various dates listed and some of the entries are just text
such as 12-Sep-05 or the words Did Not Go. However, all I need to count
are only those fields with a date in the block and not text. Any ideas?

If I understand you correctly, column C entries are either dates or the
words "Did Not Go" and I will assume all the Column C cells are
"general" formatted. Your formula will then be

=COUNTIF(C1:C100,\"<DID NOT GO\")

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=466772

Yes column C is either a date or text, and no the cells are not
"general" formatted but rather "date" formatted. It is just that some
of them have text rather than a date. I only want to count the fields
with an actual date in them for example:

Column C
24-Aug-05
No Show
01-Sep-05
12-Sep-05
No Show

I want my formula to count just the fields with a date in it and give
me a total, which in this example is three. That way I know only three
people attended. I do not need to count the fields with text or nothing
in them.

Thanks,
Fritz


--
fritzj8
------------------------------------------------------------------------
fritzj8's Profile: http://www.excelforum.com/member.php...o&userid=27167
View this thread: http://www.excelforum.com/showthread...hreadid=466772

The formula that I gave you will still work then. Simply change "Did Not
Go" to "No Show" in the formula and you will be OK.


Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=466772