Thread: COUNTIF AND
View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
PCLIVE PCLIVE is offline
external usenet poster
 
Posts: 1,311
Default 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!