COUNTIF AND
 

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 AND
 

=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!

COUNTIF AND
 

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!

COUNTIF AND
 

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 AND
 

=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!

COUNTIF AND
 

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!

COUNTIF AND
 

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!

COUNTIF AND
 

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!

COUNTIF AND
 

We can't use E22 or B31. We have to use the actual words. Here's the
formula that works:
=SUM(IF(ACTIVE!$C$2:$C$1000="1 -
Critical",IF(ACTIVE!$O$2:$O$1000="Incorrect, missing or vague requirement
specification",1,0),0))

The problem is, we have a large table to fill in and we can't just change
the words. (1-Critical to 2-Critical) by typing it in the formula. We have
to go back into Tools/Conditional Sum to rewrite the formula for every cell.
I keep thinking there has to be an easier way. Thanks for your help!

"PCLIVE" wrote:

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!

COUNTIF AND
 

Heres one possible way:

=SUMPRODUCT(--(ACTIVE!$C$2:$C$1000=ROW(A1)&"-Critical"),--(ACTIVE!$O$2:$O$1000="Incorrect,
missing or vague requirement
specification"))

The "1-Critical" will increment to "2-Critical", "3-Critical" when the
formula is copied down. Is that something that will that work for you?



--

"Kara" wrote in message
...
We can't use E22 or B31. We have to use the actual words. Here's the
formula that works:
=SUM(IF(ACTIVE!$C$2:$C$1000="1 -
Critical",IF(ACTIVE!$O$2:$O$1000="Incorrect, missing or vague requirement
specification",1,0),0))

The problem is, we have a large table to fill in and we can't just change
the words. (1-Critical to 2-Critical) by typing it in the formula. We
have
to go back into Tools/Conditional Sum to rewrite the formula for every
cell.
I keep thinking there has to be an easier way. Thanks for your help!

"PCLIVE" wrote:

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!