Fill a blank cell with a value using if conditions

[rjagathe]

I have data in 3 columns.using "if "' condition I have to, upto a
certain number, fill a value in 4th column.There may be blank cells in
4th column.Again I have to scroll down the rows and fill blank cells
in the 4th column using another "if " condition.That is I want to
fill cells in 4th column, only when it is blankHow to do it?
I have given an illustration below;

Excel Sheet contains data as follows:
A B C D
1 S M 1
2 B F 1
3 M F 2
4 S M 1
5 B M 1
6 S M 1
7 M F 2
8 S M 1
Using if condition that is if A=''S" and B="M", I fill 4th column 1st
and 4th row with a value "True1" for 2 times

A B C D
1 S M 1 True1
2 B F 1
3 M F 2
4 S M 1 True1
5 B M 1
6 S M 1
7 M F 2
8 S M 1


Again i want to use another set of If conditions such as if A=S,and
B=M ,I want to fill 4th column 6th and 8 th rows with a value "True2"
for 2 times as shown below:


A B C D
1 S M 1 True1
2 B F 1
3 M F 2
4 S M 1 True1
5 B M 1
6 S M 1 True2
7 M F 2
8 S M 1 True2

 

[Bernard Liengme]

Try
=IF(AND(A1="S",B1="M"),"True"&INT((SUMPRODUCT(--($A$1:A1="S"),--($B$1:B1="M"))+1)/2),"")
best wishes

 

[rjagathe]

Try
=IF(AND(A1="S",B1="M"),"True"&INT((SUMPRODUCT(--($A$1:A1="S"),--($B$1:B1="M "))+1)/2),"")
best wishes

Sir,
I pasted your formula in the formula bar and clicked "Enter"
key.But an error message appears stating that there are some errors.

 

[Rick Rothstein]

The formula works for me. Make sure you didn't pick up a Line Feed character
at the end of the formula when you copied it... after you paste the formula
into the Formula Bar, it should occupy only one line... if you see the text
cursor on a different line, then you picked up a Line Feed and the will make
the formula not work. To correct the problem (if this is what went wrong),
just hit the BackSpace key to remove the Line Feed (if done correctly, the
text cursor will be at the end of the formula; hit the Enter key to commit
the formula).

--
Rick (MVP - Excel)

Try
=IF(AND(A1="S",B1="M"),"True"&INT((SUMPRODUCT(--($A$1:A1="S"),--($B$1:B1="M
"))+1)/2),"")
best wishes

Sir,
I pasted your formula in the formula bar and clicked "Enter"
key.But an error message appears stating that there are some errors.

 

[rjagathe]

The formula works for me. Make sure you didn't pick up a Line Feed character
at the end of the formula when you copied it... after you paste the formula
into the Formula Bar, it should occupy only one line... if you see the text
cursor on a different line, then you picked up a Line Feed and the will make
the formula not work. To correct the problem (if this is what went wrong),
just hit the BackSpace key to remove the Line Feed (if done correctly, the
text cursor will be at the end of the formula; hit the Enter key to commit
the formula).

--
Rick (MVP - Excel)





Sir,
    I pasted your formula in the formula bar and clicked "Enter"
key.But an error message appears stating that there are some errors.

Now it works.But it returns only the value "True0" in all the 4 rows
viz., D1, D4,D6 and D8.I want "True1" in D1 and D4 and "True2" in D6
and D8.

 

[Rick Rothstein]

I'm not sure what to tell you... the formula produces True1 and True2 in the
correct locations using the sample data you posted... I can find no way for
that formula to produce True0... did you copy/paste the formula that Bernard
posted or did you try to type it in? If you tried to type it in, I'm
guessing you did so incorrectly... try copy/pasting the formula instead.

--
Rick (MVP - Excel)

The formula works for me. Make sure you didn't pick up a Line Feed
character
at the end of the formula when you copied it... after you paste the
formula
into the Formula Bar, it should occupy only one line... if you see the
text
cursor on a different line, then you picked up a Line Feed and the will
make
the formula not work. To correct the problem (if this is what went wrong),
just hit the BackSpace key to remove the Line Feed (if done correctly, the
text cursor will be at the end of the formula; hit the Enter key to commit
the formula).

--
Rick (MVP - Excel)





Sir,
I pasted your formula in the formula bar and clicked "Enter"
key.But an error message appears stating that there are some errors.

Now it works.But it returns only the value "True0" in all the 4 rows
viz., D1, D4,D6 and D8.I want "True1" in D1 and D4 and "True2" in D6
and D8.

 

[rjagathe]

I'm not sure what to tell you... the formula produces True1 and True2 in the
correct locations using the sample data you posted... I can find no way for
that formula to produce True0... did you copy/paste the formula that Bernard
posted or did you try to type it in? If you tried to type it in, I'm
guessing you did so incorrectly... try copy/pasting the formula instead.

--
Rick (MVP - Excel)







Now it works.But it returns only the value "True0" in all the 4 rows
viz., D1, D4,D6 and D8.I want "True1" in D1 and D4 and "True2" in D6
and D8.

I am using office 2007.I have copy -pasted the formula.Will it work in
office 2007.May be that is the problem.

 

[rjagathe]

I am using office 2007.I have copy -pasted the formula.Will it work in
office 2007.May be that is the problem.

I found the bug .There is an unnecessary space $B$1:B1="M ".Now it
works fine.
As an improvement to this formula ,I want "5n' number of occurrences
(not just 2) in the results showing True 1 and True 2 and so on.That
is I may want 5, "True1" , 15 "True2" and 55 "True3".Pl help.