Formula to populate number in one column based on conditions in other columns

[LtCmdrData]

I am trying to write a formula I can drag down a column so it will populate a series of numbers based on conditions in another column. I always start with "1" in the first cell (R2 because I have a column heading in R1). If column Q has data then repeat "1" in R3. If Q3 is blank then leave R3 blank. If Q4 has data then populate R4 with "2" and so on. Here is an example of what my data looks like. I think an IF or IFS statement would work? Can someone please help me write this formula? Thank you.

 

[kweaver]

Please update your profile to indicate which version of Excel you are using and please try to use XL2BB to post sample data.

What about:

Book1
	Q	R
1	Heading1	Heading2
2	USA	1
3	USA	2
4
5	USA	3
6
7	USA	4
8
9	USA	5
10
11	USA	6
12	USA	7
13
14	ABC	8
15	ABC	9
16	ABC	10
Sheet1

Cell Formulas
Range	Formula
R2:R16	R2	=IF(""=Q2,"",1+MAX($R$1:R1))

 

[johnny51981]

I have something similar with a little extra

=IF($Q2="","",IF(AND(R1<>"",R1<>$R$1),R1,IFERROR(MAX($R$1:$R1),0)+1))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option

Fluff.xlsm
	Q	R
1	Heading1	Heading2
2	USA	1
3	USA	1
4
5	USA	2
6
7	USA	3
8
9	USA	4
10
11	USA	5
12	USA	5
13
14	USA	6
15	USA	6
16	USA	6
Data

Cell Formulas
Range	Formula
R3:R16	R3	=IF(Q3="","",IF(Q2="",MAX(R$1:R2)+1,R2))

 

[LtCmdrData]

I have something similar with a little extra

=IF($Q2="","",IF(AND(R1<>"",R1<>$R$1),R1,IFERROR(MAX($R$1:$R1),0)+1))

This solution works. Thanks for the quick reply.

 

[LtCmdrData]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option

Fluff.xlsm
	Q	R
1	Heading1	Heading2
2	USA	1
3	USA	1
4
5	USA	2
6
7	USA	3
8
9	USA	4
10
11	USA	5
12	USA	5
13
14	USA	6
15	USA	6
16	USA	6
Data

Cell Formulas
Range	Formula
R3:R16	R3	=IF(Q3="","",IF(Q2="",MAX(R$1:R2)+1,R2))

Thanks Fluff, you came thru for me once again. I updated my profile as you suggested. Thanks again!

 

[Fluff]

Glad we could help & thanks for the feedback.

Thanks also for updating your profile.