COUNTIF AND

  • Thread starter Thread starter Kara
  • Start date Start date
K

Kara

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
 
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.
 
=COUNTIF(ACTIVE!C:C,E22)+COUNTIF(ACTIVE!O:O,B31)
this is the formula that adds them together successfully.
 
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

--
 
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.
 
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

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



--
 
Back
Top