Thread: 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 fritzj@interquest.de

Thanks,
Fritz

multiposted

--
Don Guillett
SalesAid Software
donaldb@281.com
"fritzj8" <fritzj8.1v91io_1126534008.6983@excelforum-nospam.com> wrote in
message news:fritzj8.1v91io_1126534008.6983@excelforum-nospam.com...
>
> 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 fritzj@interquest.de
>
> Thanks,
> Fritz
>
>
> --
> fritzj8
> ------------------------------------------------------------------------
> fritzj8's Profile:
http://www.excelforum.com/member.php...o&userid=27167
> View this thread: http://www.excelforum.com/showthread...hreadid=466777
>

=SUMPRODUCT(--('[Spreadsheet Name.xls]'!$B1:$B1000,"Germany")
,--('[Spreadsheet Name.xls]'!$A1:$A1000,"Y"))

--
HTH

Bob Phillips

"fritzj8" <fritzj8.1v91io_1126534008.6983@excelforum-nospam.com> wrote in
message news:fritzj8.1v91io_1126534008.6983@excelforum-nospam.com...
>
> 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 fritzj@interquest.de
>
> Thanks,
> Fritz
>
>
> --
> fritzj8
> ------------------------------------------------------------------------
> fritzj8's Profile:
http://www.excelforum.com/member.php...o&userid=27167
> View this thread: http://www.excelforum.com/showthread...hreadid=466777
>