I need help with a formula for the following:

Column A Column B
1 SEC
1 DEA
1 SEC
2
2
3
2

I need to count the number of SEC in Column B only if there is a
corresponding "1" in Column A. In this instance the answer would be 2.

All help would be appreciated.

Thank you

"Autumn Dreams" wrote:

I need help with a formula for the following:

Column A Column B
1 SEC
1 DEA
1 SEC
2
2
3
2

I need to count the number of SEC in Column B only if there is a
corresponding "1" in Column A. In this instance the answer would be 2.

All help would be appreciated.

Thank you


You need to count the number of "true" cases for the existence of a logical
relationship. This cannot be done with COUNT() "out of the box."

The best way to handle this (and it happens a lot) is to create a new column
that contains a formula for creating and containing the test results, and
then COUNTing the derived column.

Create a column to the left of your example with the following formula in
each cell:

=AND(A1="1",B1="SEC")

This should always resolve to TRUE or FALSE. Then COUNT the number of TRUE
values in the new column.

This is simply an example. There are a dozen variations of this that you
could use just a well and perhaps even better.

B+
HALinNY

Thank you. My apologies for not being clearer. I would then need to count
all the DEA with a corresponding "1". There are several of these to count.

Thank you.

"HALinNY" wrote:



"Autumn Dreams" wrote:

I need help with a formula for the following:

Column A Column B
1 SEC
1 DEA
1 SEC
2
2
3
2

I need to count the number of SEC in Column B only if there is a
corresponding "1" in Column A. In this instance the answer would be 2.

All help would be appreciated.

Thank you


You need to count the number of "true" cases for the existence of a logical
relationship. This cannot be done with COUNT() "out of the box."

The best way to handle this (and it happens a lot) is to create a new column
that contains a formula for creating and containing the test results, and
then COUNTing the derived column.

Create a column to the left of your example with the following formula in
each cell:

=AND(A1="1",B1="SEC")

This should always resolve to TRUE or FALSE. Then COUNT the number of TRUE
values in the new column.

This is simply an example. There are a dozen variations of this that you
could use just a well and perhaps even better.

B+
HALinNY

"Autumn Dreams" wrote:

Thank you. My apologies for not being clearer. I would then need to count
all the DEA with a corresponding "1". There are several of these to count.

Thank you.

Not a problem. This kind of thing happens all the time. Instead of the
formula I used before, use this one ...

=A1&B1

This will give you values like "1SEC" and "2DEA"

Then you can COUNT the number of cells containing the particular string you
are interested in.

B+
HALinNY

Another option, that wouldn't require any additional columns would be:

=SUMPRODUCT((A1:A100=1)*(B1:B100="SEC"))

HTH,
Elkar


"Autumn Dreams" wrote:

Thank you. My apologies for not being clearer. I would then need to count
all the DEA with a corresponding "1". There are several of these to count.

Thank you.

"HALinNY" wrote:



"Autumn Dreams" wrote:

I need help with a formula for the following:

Column A Column B
1 SEC
1 DEA
1 SEC
2
2
3
2

I need to count the number of SEC in Column B only if there is a
corresponding "1" in Column A. In this instance the answer would be 2.

All help would be appreciated.

Thank you


You need to count the number of "true" cases for the existence of a logical
relationship. This cannot be done with COUNT() "out of the box."

The best way to handle this (and it happens a lot) is to create a new column
that contains a formula for creating and containing the test results, and
then COUNTing the derived column.

Create a column to the left of your example with the following formula in
each cell:

=AND(A1="1",B1="SEC")

This should always resolve to TRUE or FALSE. Then COUNT the number of TRUE
values in the new column.

This is simply an example. There are a dozen variations of this that you
could use just a well and perhaps even better.

B+
HALinNY