rbrychckn,

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

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

will work, however.

Bernie


"rbrychckn" wrote
in message ...

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?


--
rbrychckn
------------------------------------------------------------------------
rbrychckn's Profile:
http://www.excelforum.com/member.php...fo&userid=4024
View this thread: http://www.excelforum.com/showthread...hreadid=437392

Subtracting a Countif result from a constant
 

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

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

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

Now, the only way I have been able to differentiate what has a value
and what is "" is to do a COUNTIF(BA2:BD2, ""). However, I can't use
this with an offset, because what I would need is 4. The value of the
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 this
formula?

Thanks,
rbrychckn


--
rbrychckn
------------------------------------------------------------------------
rbrychckn's Profile: http://www.excelforum.com/member.php...fo&userid=4024
View this thread: http://www.excelforum.com/showthread...hreadid=437392

One way:

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

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

In article ,
rbrychckn
wrote:

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

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

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

Now, the only way I have been able to differentiate what has a value
and what is "" is to do a COUNTIF(BA2:BD2, ""). However, I can't use
this with an offset, because what I would need is 4. The value of the
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 this
formula?

Thanks,
rbrychckn

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


"rbrychckn" wrote in message
...

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

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

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

Now, the only way I have been able to differentiate what has a value
and what is "" is to do a COUNTIF(BA2:BD2, ""). However, I can't use
this with an offset, because what I would need is 4. The value of the
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 this
formula?

Thanks,
rbrychckn


--
rbrychckn
------------------------------------------------------------------------
rbrychckn's Profile: http://www.excelforum.com/member.php...fo&userid=4024
View this thread: http://www.excelforum.com/showthread...hreadid=437392

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?


--
rbrychckn
------------------------------------------------------------------------
rbrychckn's Profile: http://www.excelforum.com/member.php...fo&userid=4024
View this thread: http://www.excelforum.com/showthread...hreadid=437392