Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
EXCEL:NUMBER TO GREEK WORDS | Excel Worksheet Functions | |||
Amount or Numbers in Words | New Users to Excel | |||
Is there a formula to spell out a number in excel? | Excel Worksheet Functions | |||
Convert Numeric into Text | Excel Worksheet Functions |