I am trying to write a formula that uses AND and COUNTIF. I want it to count
how many have 1-critical AND incorrect value. I have the COUNTIF working, but
don't know how to connect them properly. Thanks for the help!

=SUMPRODUCT(--(A1:A100="1-critical"),--(B1:B100="incorrect value")

Adjust to suit
You can not use whole columns prior to XL-2007


"Kara" wrote:

I am trying to write a formula that uses AND and COUNTIF. I want it to count
how many have 1-critical AND incorrect value. I have the COUNTIF working, but
don't know how to connect them properly. Thanks for the help!

It seems this formula adds them together. I need it to count the number of
instances that "1-critical" AND "incorrect value" appear in the same row.
Sorry if I wasn't clear. It needs to have both values and then count them.

"Teethless mama" wrote:

=SUMPRODUCT(--(A1:A100="1-critical"),--(B1:B100="incorrect value")

Adjust to suit
You can not use whole columns prior to XL-2007


"Kara" wrote:

I am trying to write a formula that uses AND and COUNTIF. I want it to count
how many have 1-critical AND incorrect value. I have the COUNTIF working, but
don't know how to connect them properly. Thanks for the help!

What is the COUNTIF formula you are using for each?

--

"Kara" wrote in message
...
I am trying to write a formula that uses AND and COUNTIF. I want it to
count
how many have 1-critical AND incorrect value. I have the COUNTIF working,
but
don't know how to connect them properly. Thanks for the help!

=COUNTIF(ACTIVE!C:C,E22)+COUNTIF(ACTIVE!O:O,B31)
this is the formula that adds them together successfully.

"PCLIVE" wrote:

What is the COUNTIF formula you are using for each?

--

"Kara" wrote in message
...
I am trying to write a formula that uses AND and COUNTIF. I want it to
count
how many have 1-critical AND incorrect value. I have the COUNTIF working,
but
don't know how to connect them properly. Thanks for the help!

I'm pretty sure you want to use SUMPRODUCT for this.

=SUMPRODUCT(--(ACTIVE!C1:C10=E22),--(ACTIVE!O1:O10=B31))

The formula will count the number of rows that contain both criteria.
Specifically, the number of rows where the value in E22 exist in the column
C range AND where the value in B31 exists in the column O range. It only
counts if both criteria are met. In order to use SUMPRODUCT, the range must
be specified (for most XL versions). Adjust the range as necessary.

Does that help?
Paul

--

"Kara" wrote in message
...
=COUNTIF(ACTIVE!C:C,E22)+COUNTIF(ACTIVE!O:O,B31)
this is the formula that adds them together successfully.

"PCLIVE" wrote:

What is the COUNTIF formula you are using for each?

--

"Kara" wrote in message
...
I am trying to write a formula that uses AND and COUNTIF. I want it to
count
how many have 1-critical AND incorrect value. I have the COUNTIF
working,
but
don't know how to connect them properly. Thanks for the help!

It is not working. We also are trying to just fill in the rest of the table
by dragging instead of typing. We found that conditional sum works, but we
have to go in and reset it for every cell using the tools/conditional sum.
It won't work right if we just go type in the words from B31.

"PCLIVE" wrote:

I'm pretty sure you want to use SUMPRODUCT for this.

=SUMPRODUCT(--(ACTIVE!C1:C10=E22),--(ACTIVE!O1:O10=B31))

The formula will count the number of rows that contain both criteria.
Specifically, the number of rows where the value in E22 exist in the column
C range AND where the value in B31 exists in the column O range. It only
counts if both criteria are met. In order to use SUMPRODUCT, the range must
be specified (for most XL versions). Adjust the range as necessary.

Does that help?
Paul

--

"Kara" wrote in message
...
=COUNTIF(ACTIVE!C:C,E22)+COUNTIF(ACTIVE!O:O,B31)
this is the formula that adds them together successfully.

"PCLIVE" wrote:

What is the COUNTIF formula you are using for each?

--

"Kara" wrote in message
...
I am trying to write a formula that uses AND and COUNTIF. I want it to
count
how many have 1-critical AND incorrect value. I have the COUNTIF
working,
but
don't know how to connect them properly. Thanks for the help!

I'm not sure I understand. Does the formula work the first time applied?
Does it stop after dragging the formula to other cells? What exactly
doesn't work? If dragging the formula, I'm assuming that the range should
remain the same. So are the E22 and B31 references supposed to
change?...and which way. E23, E24, etc...and B32, B34, etc.?

Understanding what you're trying to do will help come up with the
appropriate formula.

Regards,
Paul

--

"Kara" wrote in message
...
It is not working. We also are trying to just fill in the rest of the
table
by dragging instead of typing. We found that conditional sum works, but
we
have to go in and reset it for every cell using the tools/conditional sum.
It won't work right if we just go type in the words from B31.

"PCLIVE" wrote:

I'm pretty sure you want to use SUMPRODUCT for this.

=SUMPRODUCT(--(ACTIVE!C1:C10=E22),--(ACTIVE!O1:O10=B31))

The formula will count the number of rows that contain both criteria.
Specifically, the number of rows where the value in E22 exist in the
column
C range AND where the value in B31 exists in the column O range. It only
counts if both criteria are met. In order to use SUMPRODUCT, the range
must
be specified (for most XL versions). Adjust the range as necessary.

Does that help?
Paul

--

"Kara" wrote in message
...
=COUNTIF(ACTIVE!C:C,E22)+COUNTIF(ACTIVE!O:O,B31)
this is the formula that adds them together successfully.

"PCLIVE" wrote:

What is the COUNTIF formula you are using for each?

--

"Kara" wrote in message
...
I am trying to write a formula that uses AND and COUNTIF. I want it
to
count
how many have 1-critical AND incorrect value. I have the COUNTIF
working,
but
don't know how to connect them properly. Thanks for the help!