Subtracting a Countif result from a constant

  • Thread starter Bernie Deitrick
  • Start date
B

Bernie Deitrick

rbrychckn,

No, I messed up - it returns 4 no matter what:

=SUMPRODUCT((A2:D2<>"")*1)

will work, however.

Bernie
 
R

rbrychckn

I've been trying this for days, and feel like I'm missing somethin
trivial.

I am pulling Status values (Complete, Incomplete, Scheduled) fro
various sheets for a given person's file onto a Master sheet, usin
Index/Match formulas. If there is no Status given for a stage/person
the value of these formulas will be "". There are four statuses pe
person in columns BA through BD (where these formulas reside). I wan
to be able to take the last status for each person (in BE). Here i
some sample data:

BA BB BC BD BE
Stage1 Stage2 Stage 3 Stage 4 Total
C C C I Stage 4 Incomplete
S Stage
Scheduled

Now, the only way I have been able to differentiate what has a valu
and what is "" is to do a COUNTIF(BA2:BD2, ""). However, I can't us
this with an offset, because what I would need is 4. The value of th
offset, however, is 0. What I would need is essentially y=-x+4 (ie
0--> offset 4, 1-->3, 2-->2, etc). Can anyone help me with thi
formula?

Thanks,
rbrychck
 
J

JE McGimpsey

One way:

="Stage " & COUNTIF(A3:D3,">""") & " " & LOOKUP(INDEX(A3:D3,
COUNTIF(A3:D3,">""")),{"C","I","S"},{"Complete","Incomplete","Scheduled"}
)

=TEXT(LEN(A3&B3&C3&D3),"""Stage ""0 ") & LOOKUP(INDEX(A3:D3,
LEN(A3&B3&C3&D3)), {"C","I","S"}, {"Complete","Incomplete","Scheduled"})
 
B

Bernie Deitrick

rbrychckn,

Is this what you want:

=4-COUNTIF(BA2:BD2, "")

Though

=COUNTIF(BA2:BD2, "<>""")

would be better, since you aren't tied to a constant. (4)

HTH,
Bernie
MS Excel MVP
 
R

rbrychckn

Ah. I didn't realize you could create such a argument in a countif (eg.
"<>""")

This doesn't work, however, in my sheet. I'm using Excel 2000 - was
this function syntax added after?
 
Top