sumif contains questions

[1buckeyefan]

Greetings,
I have a question and could use some help with an excel function.

Here is what I'm trying to do. If column A contains '*001' then B=A otherwise B=A1+1

A B
1 1980001 1980001
2 1980002 1980002
3 1980003 1980003
4 1987001 1987001
5 1987002 1987002


I just can't seem to figure this one out.

Thanks!

 

[wideboydixon]

Book1
	A	B
1	1980001	1980001
2	1980002	1980003
3	1980003	1980004
4	1987001	1987001
5	1987002	1987003
Sheet1

Cell Formulas
Range	Formula
B1	=IF(MOD($A1,1000)=1,$A1,$A1+1)

WBD

 

[oldbrewer]

[TABLE="width: 545"]
<colgroup><col><col span="6"></colgroup><tbody>[TR]
[TD]1980001 1980001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2 1980002 1980002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3 1980003 1980003[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4 1987001 1987001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5 1987002 1987002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]col A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1980001[/TD]
[TD="align: right"]1980001[/TD]
[TD]#####[/TD]
[TD]row 9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1980002[/TD]
[TD="align: right"]1980002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1980003[/TD]
[TD="align: right"]1980002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1987001[/TD]
[TD="align: right"]1980001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1987002[/TD]
[TD="align: right"]1980002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]#####[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 6"]=IF(RIGHT(TEXT(A9,"0000000"),3)="001", $A$9,$A$9+1)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[1buckeyefan]

Thanks for the quick response! I should have given a bit more detail. The formula will need to be applied to every cell in Column B. So for your example, MOD($A1,1000) does = 1 but for the rest of values, this would not work. So if the MOD does not equal 1 can I just add 1 to the number in column A for the value of Column B?

 

[oldbrewer]

then use RIGHT(TEXT(A9,"0000000"),1)

 

[wideboydixon]

Thanks for the quick response! I should have given a bit more detail. The formula will need to be applied to every cell in Column B. So for your example, MOD($A1,1000) does = 1 but for the rest of values, this would not work. So if the MOD does not equal 1 can I just add 1 to the number in column A for the value of Column B?

That's what my formula does if you copy it down. Did you try it? Did you look at my example output? Is that not what you want?

WBD